Hi everyone,
I am trying to delete rows based on a value designated in another worksheet --> Enter Info. There are multiple criteria on this worksheet cells A2:A10. The code I have now deletes the rows successfully, however, if the cell in A2 is blank then the program does not run. My question is, how can I get my code to ignore this criteria and move onto the next criteria in A3. Is it not possible to use an "If" statement within a "With" statement?
I am trying to delete rows based on a value designated in another worksheet --> Enter Info. There are multiple criteria on this worksheet cells A2:A10. The code I have now deletes the rows successfully, however, if the cell in A2 is blank then the program does not run. My question is, how can I get my code to ignore this criteria and move onto the next criteria in A3. Is it not possible to use an "If" statement within a "With" statement?
Code:
Option Explicit
Public Sub DeleteRowsWithAutofilter()
Dim wksData As Worksheet
Dim lngLastRow As Long
Dim rngData As Range
Dim PubCrit1 As Object
Dim PubCrit2 As Object
Dim EnterInf As Worksheet
'Set references up-front
Set wksData = ThisWorkbook.Worksheets("Master Publisher Content")
Set EnterInf = ThisWorkbook.Worksheets("Enter Info")
'Identify the last row and use that info to set up the Range
With wksData
lngLastRow = .Range("C" & .Rows.Count).End(xlUp).Row
Set rngData = .Range("C2:C" & lngLastRow)
End With
'.AutoFilter method to delete the rows
Application.DisplayAlerts = False
With rngData
If IsEmpty(ThisWorkbook.Worksheets("Enter Info").Range("A2").Value)) = true then do nothing
Else if
.AutoFilter Field:=1, _
Criteria1:=EnterInf.Range("A2").Value
'Delete the visible rows while keeping the header
.Offset(1, 0).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Rows.Delete
End If
End With
Application.DisplayAlerts = True
'Turn off the AutoFilter
With wksData
.AutoFilterMode = False
If .FilterMode = True Then
.ShowAllData
End If
End With
Last edited by a moderator: