Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
This should be simple but...
I have a spreadsheet with thousands of lines of data
I need to remove some lines of data The rule is... If any cell in column A has a background colour that's not the default (blank) then delete the whole row. I know this should be a simple loop but recording a macro doesn't seem to give me any clues. Any help would be appreciated. Jeff |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
This should be simple but...
Jeff, if I understood you correctly this should do the trick:
Sub erase_rows() lastrow = ActiveSheet.UsedRange.Rows.Count For i = lastrow To 1 Step -1 cell = "A" & i Range(cell).Select If Selection.Interior.ColorIndex < xlNone Then Rows(i).Select Selection.Delete Shift:=xlUp End If Next i End Sub "Jeff" wrote: I have a spreadsheet with thousands of lines of data I need to remove some lines of data The rule is... If any cell in column A has a background colour that's not the default (blank) then delete the whole row. I know this should be a simple loop but recording a macro doesn't seem to give me any clues. Any help would be appreciated. Jeff |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
This should be simple but...
Jeff
Something like this should work. Cells with the default background (pattern?) have the PatternColorIndex of -4142, not my first guess but I was able to use the immediate pane to find out). Select the applicable rows and run this simple code. Sub test() Dim cl As Range For Each cl In Selection If Not (cl.Interior.PatternColorIndex = -4142) Then cl.EntireRow.Delete Next cl End Sub Good luck. Ken Norfolk, Va |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
This should be simple but...
Thanks Ken
That worked reasonably For some reason, deleting rows from the selection made the macro "jump" rows, but I got round that by simply running the macro 50 times! Job done thanks. I'll now go away and brush up on how to retrieve cell properties. Jeff Jeff Something like this should work. Cells with the default background (pattern?) have the PatternColorIndex of -4142, not my first guess but I was able to use the immediate pane to find out). Select the applicable rows and run this simple code. Sub test() Dim cl As Range For Each cl In Selection If Not (cl.Interior.PatternColorIndex = -4142) Then cl.EntireRow.Delete Next cl End Sub Good luck. Ken Norfolk, Va |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF formula-simple question; simple operator | Excel Discussion (Misc queries) | |||
Simple problem, simple formula, no FUNCTION ! | Excel Worksheet Functions | |||
Simple Simple Excel usage question | Excel Discussion (Misc queries) | |||
Make it more simple or intuitive to do simple things | Charts and Charting in Excel | |||
simple question, hopefully a simple answer! | Excel Programming |