C
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
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