Why have you started another thread?

"childofthe1980s" wrote:

Hello:

Below, I have code to which I have added to a macro to remove rows that
contain either the word "Grand" or "#VALUE!" in column A.

I got this code by "cheating". Specifically, I mimicked the use of Excel's
Auto Filtering in a macro.

This is the last piece of a very big puzzle that I have spent over two days
working on. (I did not realize that this would take so long.)

What I need to end this (hopefully once and for all) is to remove the two
ranges that are mentioned in the code below. You see, I need for this
filtering to remove rows meeting the "Grand" and "#VALUE!" criteria that I
mentioned earlier in this posting. You see, I don't have a specific range in
mind. I just need for Excel to use in its auto filtering in the code below
to remove rows at the end of my spreadsheet that say either "Grand" or
"#VALUE!" in cells in column A.

Right now, as you can see from the code below, "Grand" appears in row A667
while "#VALUE!" appears beginning in row A668. Next time, though, this data
may appear in A800, A450, A900, Awhatever.....I don't have a set range in
mind.

I don't know VBA syntax very well, but I'm guessing that it's just a matter
of taking out "("A668:D668")" and ("A667:D667") and replacing these two lines
of the code with whatever syntax that says "any row meeting this criteria".

Any help would be much appreciated!

Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="#VALUE!"
Range("A668:D668").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.EntireRow.Delete
Selection.AutoFilter Field:=1, Criteria1:="Grand"
Range("A667:D667").Select
Selection.EntireRow.Delete
Selection.AutoFilter Field:=1
Cells.Select
Selection.Copy
Sheets("Sheet3").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("A1").Select
Columns("A:A").EntireColumn.AutoFit
Columns("B:B").EntireColumn.AutoFit
Columns("C:C").EntireColumn.AutoFit
Columns("D:D").EntireColumn.AutoFit