FORGET IT!

I don't understand these attacks on the newsgroups, Rick (and Mike H).

I agree that it's a different posting.....that was my point by starting a
new thread. Then, before even getting my...ahem....new posting question
answered, references to my old questions are made. Sure this ain't personal?

I guess I can't come to this newsgroup to ask any questions or post any new
threads without being ganged up on. Must be lack of Christmas cheer, I
guess.

Oh, well...there are other newsgroups that I can try.

Merry Christmas, anyway.

childofthe1980s

"Rick Rothstein" wrote:

This is a COMPLETELY different question than you asked in your first thread
(posted about and hour-and-a-half earlier). In that first posting, there was
only one cell with the word Grand in it... now you are saying each cell has
one of two "words" in them... either "Grand" or "#VALUE!". Let's see if you
can clarify some things we will need to know for us..

If I understand your situation correctly, you have a worksheet with "real"
data on it before you run your main macro, right? Then your macro runs and
new rows are added after the "real" data apparently now with either "Grand"
or "#VALUE!" appearing in each(?) cell in Column A for these new rows of
"fake" data, right? Assuming you answered yes to these questions, then...
Can there be a #VALUE! error in any cells within your "real" data? When and
how do either of these words get into their cells... by code in your main
macro or do they get put there manually by the user?

--
Rick (MVP - Excel)


"childofthe1980s" wrote in
message ...
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


.