?
Solved

Excel Row Deletion Based on Fill

Posted on 2014-12-09
8
Medium Priority
?
103 Views
Last Modified: 2015-01-15
Oh great and knowledgeable people of EE:

I want to add a little bit of code to an existing Excel macro that will do the following.
1) Select all content.
2) Review each line individually to determine if the row contains a cell with the default gold fill.
3) If the row does NOT contain a cell with gold fill, the row should be deleted.
4) The code then moves through the remaining content, repeating the deletion process for all rows that do not contain a cell with gold fill.

The end result is that only rows that contain at least one cell with gold fill remain.

As always, your assistance is greatly appreciated.
0
Comment
Question by:behest
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 26

Expert Comment

by:Nick67
ID: 40490273
an existing Excel macro
Do you already have looping/filtering code in place that can be flanged in?
Select all content.
Is there a set row & column count?  Rows only, Columns only?

You can build loops to check each cell row by row, but that is ugly-slow code if the sheet is huge.
More detail, and a sample are a good idea!
0
 

Author Comment

by:behest
ID: 40490352
Thank you for considering my latest challenge. Attached is an example of the basic functionality of the macro. After running the macro, there is one column sorted by color. But another column also contains gold filled cells. All I really want to be left at the end of the process is the rows that have gold cells.
Gold-Fill-Sample.xlsm
0
 
LVL 10

Expert Comment

by:bromy2004
ID: 40490548
Sub Macro1()
Dim i As Long
Dim r As Range

Set r = ActiveSheet.Range("B1:B200")
For i = r.Cells.Count To 1 Step -1
    If r.Cells(i).Interior.ColorIndex = 6 Then
        r.Cells(i).EntireRow.Delete shift:=xlUp
    End If
Next i

End Sub

Open in new window


Change ActiveSheet.Range("B1:B200") to be the cells you want to detect.
Also double check the ColorIndex.
6 = Yellow.
Record a Macro changing the colour and use that colour index
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 26

Expert Comment

by:Nick67
ID: 40492461
This is the best I can do for you:
Define a function
Function IntColor(rng As Range) As Long
IntColor = rng.Interior.Color
End Function


Now, in the last column, whether by VBA or by hand add a formula
The formula for Row 2, last cell would be
=or(IntColor(c2) = 49407,IntColor(D2) = 49407)

Then filter by that row = False
The delete all visible rows and remove the filter

Sorry
0
 

Author Comment

by:behest
ID: 40492772
Hmmmm...neither of these options quite gets me where I want to be at the end. So, how about this option?

Instead of a filter, I apply a tiered sort, where the sort is made by cell color.

Sub GoldMacro()
'
' GoldMacro Macro
'

'
    Sheets("Gold").Select
    With Application.ReplaceFormat.Interior
        .PatternColorIndex = x1Automatic
        .Color = 49407
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
   
    Cells.Replace What:="Acrid", Replacement:="Acrid", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=True
    Cells.Replace What:="Hemp", Replacement:="Hemp", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=True

    Cells.Select
    ActiveWorkbook.Worksheets("Gold").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Gold").Sort.SortFields.Add(Range("D2:D21"), _
        xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, _
        192, 0)
    ActiveWorkbook.Worksheets("Gold").Sort.SortFields.Add(Range("C2:C21"), _
        xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, _
        192, 0)
    With ActiveWorkbook.Worksheets("Gold").Sort
        .SetRange Range("A1:F21")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A1").Select
End Sub


So, now all that remains to be done is to code the following:

-- Identify the last row with a gold fill applied.
-- Select the rows beneath the last row with gold fill.
-- Delete selected rows.
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 2000 total points
ID: 40492819
Or invert your sort.  Everything WITHOUT gold to the top.
Then you can kill everything from row 1 to the first gold cell
That might be more efficient than locating last cell -- which can be notoriously imprecise.
0
 
LVL 48

Expert Comment

by:Martin Liss
ID: 40550845
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question