Solved

Excel Row Deletion Based on Fill

Posted on 2014-12-09
8
92 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
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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 46

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

910 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now