Solved

Excel Row Deletion Based on Fill

Posted on 2014-12-09
8
91 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 26

Expert Comment

by:Nick67
Comment Utility
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
Comment Utility
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
Comment Utility
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 45

Expert Comment

by:Martin Liss
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This very simple solution applies to a narrow cross-section of the "needs to close" variety. In this case, the full message in Event Viewer was in applog, Event ID 1000: Faulting application iexplore.exe, version 8.0.6001.18702, faulting module …
User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
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 in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

771 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

7 Experts available now in Live!

Get 1:1 Help Now