Delete headings
Posted by Deb on February 13, 2002 6:49 AM
Data has headers and footers spreadout the excel sheet.
I would like a macro that recognizes several indicators and delete the rows that have them. Some of the indicators are *,Total, and transaction.
Found this handy code, but cannot seem to modifiy it.
New to this programming stuff.
Posted by Deb on February 13, 2002 6:50 AM
Left out the code:
Sub DeleteRowsBasedOnCriteria()
'Assumes the list has a heading
With ActiveSheet
'If filters are not visible then turn them on
If .AutoFilterMode = False Then .Cells(1, 1).AutoFilter
'Set the filters in A1 to show only rows to delete
.Cells(1, 1).AutoFilter Field:=1, Criteria1:="Delete"
'Delete all visible cells under the heading.
.Cells(1, 1).CurrentRegion.Offset(1, 0).SpecialCells _
(xlCellTypeVisible).EntireRow.Delete
'Remove filters
.AutoFilterMode = False
End With
End Sub
Posted by Tom Urtis on February 13, 2002 7:53 AM
Hello Deb,
You may want to do this in one fell swoop using this code, which also avoids loops. Modify the code for range set, and if you want to include other criteria to ferret out and delete.
'''''''''''''''''''''''''''''''''''''''''''''''
Sub DeleteRows()
Dim Rng As Range
Set Rng = Range("A2", Range("A65536").End(xlUp))
With Rng
.Replace What:="Total", Replacement:="", LookAt:=xlWhole
.Replace What:="~*", Replacement:="", LookAt:=xlWhole
.Replace What:="transaction", Replacement:="", LookAt:=xlWhole
End With
'Error trap in case there is nothing to delete
On Error Resume Next
Rng.SpecialCells(xlBlanks).EntireRow.Delete
End Sub
---------------------------------------------
Any help?
Tom Urtis
