Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Excel Row Deletion Based on Fill

Posted on 2014-12-09
8
Medium Priority
?
107 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
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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 49

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

Technology Partners: 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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
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.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

609 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