S
Spy128Bit
Instead of looping through every row as seen in my current script
below I'm trying to combine the use of autofilter but am having
difficulty in getting the use of "Left(Cells(RowNdx, "A"), 9) Like
"#########"" right in the macro. Any help would be appreciated or any
other ideas can be tried. Thank in advance!
http://www.rondebruin.nl/delete.htm#AutoFilter
Sub Delete_with_Autofilter()
Dim DeleteValue As String
Dim rng As Range
DeleteValue = "ron"
' This will delete the rows with "ron" in the Range("A1:A100")
With ActiveSheet
.Range("A1:A100").AutoFilter Field:=1, Criteria1:=DeleteValue
With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Delete
End With
.AutoFilterMode = False
End With
End Sub
Sub Auto_TC_DelExtra()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim Lastrow As Long
Dim RowNdx As Long
With Sheets("Data")
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For RowNdx = Lastrow To 1 Step -1
If Not (Left(Cells(RowNdx, "A"), 9) Like "#########") Then
Rows(RowNdx).Delete
End If
Next RowNdx
End With
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
below I'm trying to combine the use of autofilter but am having
difficulty in getting the use of "Left(Cells(RowNdx, "A"), 9) Like
"#########"" right in the macro. Any help would be appreciated or any
other ideas can be tried. Thank in advance!
http://www.rondebruin.nl/delete.htm#AutoFilter
Sub Delete_with_Autofilter()
Dim DeleteValue As String
Dim rng As Range
DeleteValue = "ron"
' This will delete the rows with "ron" in the Range("A1:A100")
With ActiveSheet
.Range("A1:A100").AutoFilter Field:=1, Criteria1:=DeleteValue
With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Delete
End With
.AutoFilterMode = False
End With
End Sub
Sub Auto_TC_DelExtra()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim Lastrow As Long
Dim RowNdx As Long
With Sheets("Data")
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For RowNdx = Lastrow To 1 Step -1
If Not (Left(Cells(RowNdx, "A"), 9) Like "#########") Then
Rows(RowNdx).Delete
End If
Next RowNdx
End With
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub