Solved

Excel Row Deletion Based on Fill

Posted on 2014-12-09
8
97 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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 500 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 47

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This collection of functions covers all the normal rounding methods of just about any numeric value.
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

732 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