MrExcel.com - Your One Stop for Excel Tips & Solutions

Easier way to do Macro

Posted by Jimmy on September 05, 2001 9:29 AM

Here is a macro that I recorded, is there a better way to do this, so maybe some of this can be combined and have it do the deletes all at the same time rather than having to filter each time then delete?

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 09/05/2001 by Jimmy
'

'
Selection.AutoFilter Field:=1, Criteria1:="9"
Rows("2:2000").Select
Selection.Delete Shift:=xlUp
Selection.AutoFilter Field:=1, Criteria1:="A"
Rows("2:2000").Select
Selection.Delete Shift:=xlUp
Selection.AutoFilter Field:=1, Criteria1:="D"
Rows("2:2000").Select
Selection.Delete Shift:=xlUp
Selection.AutoFilter Field:=1, Criteria1:="DD"
Rows("2:2000").Select
Selection.Delete Shift:=xlUp
Selection.AutoFilter Field:=1, Criteria1:="R"
Rows("2:2000").Select
Selection.Delete Shift:=xlUp
Selection.AutoFilter Field:=1, Criteria1:="Q"
Rows("2:2000").Select
Selection.Delete Shift:=xlUp
Selection.AutoFilter Field:=1, Criteria1:="F"
Rows("2:2000").Select
Selection.Delete Shift:=xlUp
End Sub

Posted by Juan Pablo on September 05, 2001 9:45 AM

Macro2 Macro Macro recorded 09/05/2001 by Jimmy


I wouldn't go with a macro, i think it's easier using Advanced Filter.

In Cells A2002 (Or in another sheet) put the name of the Field1 (EXACTLY THE SAME). Then in Cells A2003:A2009 copy your criteria (9;A;DD;R;Q;F). Then go to Data - Filer - Advanced Filter.

In Action select first option. In range of lists select your data (A2:Z2000 or something like that) and in range of criteria select A2002:A2009. Click Accept and you have the list selected with this criteria. Now you can select all the rows, select Edition - Go To, Special, Visible cells. Now erase the entire rows and you're done.

Looks complicated but it isn't

Juan Pablo

Posted by Juan Pablo on September 05, 2001 9:49 AM

Or you can try this macro...

Sub EraseRows()
Range("A2:Z2000").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Range("A2002:A2009"), Unique:=False
Rows("2:2000").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Delete Shift:=xlUp
ActiveSheet.ShowAllData
End Sub


REPLACE YOUR OWN RANGE (A2:Z2000) and your done.

Juan Pablo Macro2 Macro Macro recorded 09/05/2001 by Jimmy

Posted by Russell Hauf on September 05, 2001 9:58 AM

Try the following (the single quotes are my [ugly] way of indenting the code -- you can just do a find and replace on the single quote and replace it with a space).

Sub DeleteCertainRows()

Dim intRow As Integer

For intRow = 2000 To 1 Step -1

''''Select Case Cells(intRow, 1).Text
''''Case "9", "A", "D", "DD", "F", "Q", "R"
''''''''Rows(intRow).Select
''''''''Selection.EntireRow.Delete
''''Case Else
''''End Select

Next intRow

Range("A1").Select

End Sub


You had "Field=1" in your criteria statement, so I assumed that the data was in column A (or column 1). If it is not, change the 1 in "Cells(intRow, 1)" to the column number where the delete criteria is.


Hope this helps,

Russell

Posted by Jimmy on September 05, 2001 11:42 AM

Works perfectly, Thanks

Posted by Jimmy on September 05, 2001 11:45 AM

Thanks, but for some reason when I run your macro I get a runtime error the line activesheet.showalldata, then all my data is gone. I do appreciate the help, but Russell has me fixed up.

Posted by Juan Pablo on September 05, 2001 12:00 PM

THe Range should be from A1:A2000 instead of A2:A2000, that takes care of it. Thanks, but for some reason when I run your macro I get a runtime error the line activesheet.showalldata, then all my data is gone. I do appreciate the help, but Russell has me fixed up.