removing rows but not based on a range

[childofthe1980s]

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 "("A668668")" and ("A667667") 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("A668668").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.EntireRow.Delete
Selection.AutoFilter Field:=1, Criteria1:="Grand"
Range("A667667").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").EntireColumn.AutoFit

 

[Mike H]

Why have you started another thread?

 

[childofthe1980s]

Because, you never can tell on these newsgroups who is available and who is
not. Truthfully, how was I to know that you were still available?

In any case, I have this situation whittled down to this last little bit.
If I can find anybody who can help me modify these two lines of this last
little bit of code, then I would be all set. And, I can pursue other
interests that I have neglected for two days because of this.

childofthe1980s

 

[Rick Rothstein]

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?

 

[Mike H]

I've given you an answer in my modified code in your other thread and if that
doesn't work a more useful description of what went wrong would be helpful

 

[childofthe1980s]

You're taking that, personally.

But, to answer your question, when I used your code I got the same results
as if I had never used your code in the first place.

So, I found a workaround through autofiltering. And, since the topic is
slightly different, I thought that I could start a new thread here and get
some help in this newsgroup without being accosted.

 

[childofthe1980s]

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]

There are no attacks taking place... the problem is that your question is
not well defined (at least, not to me)... we are just trying to understand
the parameters under which you are working in order to give you the best
answer we can. Remember, you **know** what you have and where everything
is... we here, on the other hand, only know what you tell us... things you
may be taking for granted might be necessary for us to know in order to give
you a workable answer (the addition of the #VALUE! is a significant addition
to the information you gave us in your initial posting). The AutoFilter
method you are trying to pursue is probably not the best approach, but that
is hard to say without knowing more than you have told us so far. The
questions I asked in my last post were serious attempts to find out what you
actually are trying to do. With that in mind, I do have one more question to
add to the others I asked you in my previous post... can the first cell
after your "real" data ever contain #VALUE! or will it always contain
"Grand". If you will provide answer to all the questions I have asked, I
think we can give you a solution for what you want to do.

 

[Charabeuh]

'Hello !
'Try This:
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub DeleteRows()
Dim LastRow, i
LastRow = Range("A" & Rows.Count).End(xlUp).Row

For i = LastRow To 1 Step -1
If Application.WorksheetFunction.IsErr(Range("A" & i)) Then
Rows(i).Delete
ElseIf Range("A" & i) = "Grand" Then
Rows(i).Delete
Else
Exit For
End If
Next i

End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Happy Christmas !




"childofthe1980s" <[email protected]> a écrit dans
le message de groupe de discussion :
(e-mail address removed)...

 

[Mike H]

You're taking that, personally.

No I'm not, like everyone else who attempts to provide solutions here I'm
trying to help. Notwithstanding the additional information you've provided,
I'll ask again,did you try the 'second' version of my code I posted in your
other thread? What result did you get?

Mike

 

[childofthe1980s]

Fortunately, I figured this out on my own. For your reference, here is the
code:

I had to (a) arrow down to the cell A2 before filtering out "Grand" and (b)
do Shift-Ctrl-End for both the "Grand" and "#VALUE!" criteria. That way, VBA
does not reference specific cells!

This is all I needed! Merry Christmas to All and, after having worked on
this all day, to All a Goodnight!!!!!!!

Selection.AutoFilter
Range("A2").Select
Selection.AutoFilter Field:=1, Criteria1:="Grand"
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.EntireRow.Delete
'Selection.AutoFilter Field:=1, Criteria1:="Grand"
'Rows("623:623").Select
'Selection.Delete Shift:=xlUp
Selection.AutoFilter Field:=1, Criteria1:="#VALUE!"
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.EntireRow.Delete
Selection.AutoFilter Field:=1

 

[childofthe1980s]

Fortunately, I figured this out on my own. For your reference, here is the
code:

I had to (a) arrow down to the cell A2 before filtering out "Grand" and (b)
do Shift-Ctrl-End for both the "Grand" and "#VALUE!" criteria. That way, VBA
does not reference specific cells!

This is all I needed! Merry Christmas to All and, after having worked on
this all day, to All a Goodnight!!!!!!!

Selection.AutoFilter
Range("A2").Select
Selection.AutoFilter Field:=1, Criteria1:="Grand"
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.EntireRow.Delete
'Selection.AutoFilter Field:=1, Criteria1:="Grand"
'Rows("623:623").Select
'Selection.Delete Shift:=xlUp
Selection.AutoFilter Field:=1, Criteria1:="#VALUE!"
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.EntireRow.Delete
Selection.AutoFilter Field:=1

 

[childofthe1980s]

Fortunately, I figured this out on my own. For your reference, here is the
code:

I had to (a) arrow down to the cell A2 before filtering out "Grand" and (b)
do Shift-Ctrl-End for both the "Grand" and "#VALUE!" criteria. That way, VBA
does not reference specific cells!

This is all I needed! Merry Christmas to All and, after having worked on
this all day, to All a Goodnight!!!!!!!

Selection.AutoFilter
Range("A2").Select
Selection.AutoFilter Field:=1, Criteria1:="Grand"
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.EntireRow.Delete
'Selection.AutoFilter Field:=1, Criteria1:="Grand"
'Rows("623:623").Select
'Selection.Delete Shift:=xlUp
Selection.AutoFilter Field:=1, Criteria1:="#VALUE!"
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.EntireRow.Delete
Selection.AutoFilter Field:=1