How to ignore a blank cell with the criteria with the .Autofilter method

Alex89

New Member
Joined
May 30, 2019
Messages
34
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?


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:

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
My apologies for not responding sooner, this worked perfectly. I realized the variable name was EnterInf instead of EnterInfo. Thank you so much for your help, I really appreciate it.

In the if statement, what is the logic behind using <> ""
 
Upvote 0
Glad you figured it out & thanks for the feedback.

That line is saying if the cell isn't blank, then run the autofilter
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,334
Members
452,636
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top