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

[Alex89]

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?

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

 

[Fluff]

How about

Dim Cl As Range
With rngdata
   For Each Cl In enterinfo.Range("A2:A10")
      If Cl.Value <> "" Then
         .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
   Next Cl
End With

 

[Alex89]

Hi,

Thanks for taking the time to reply, I really appreciate it!

Unfortunately, I am getting a similar error message when I try to run the code "No cells were found."

I'm trying to use the cells in A2:A10 as criteria for deleting sheet rows in column C in another worksheet (rngData). I want VBA to ignore the code (if a cell in A2:A10 has a blank) and move onto the next cell from A2, A3, A4, A5, A6 etc. whether the cell has a blank or not.

 

[Fluff]

If any cell in A2:A10 is blank then it will skip that cell and move onto the next one.
It sounds like the data in one or more of the cells A2:A10 is not in col C

 

[Alex89]

The cells in A2:A10 were created by removing the duplicates in column C, creating only unique values. So the cells in A2:A10 are all in Column C.

So for example, if I delete the value in cell A2 in the criteria worksheet - I want all of the sheet rows to be deleted with the exception of the rows where A2 is found in column C.

 

[Fluff]

If you delete the value in A2, you have nothing to filter on.

 

[Alex89]

I apologize for being unclear.

I would be filtering based on the values from A3:A10

So I have 8 unique values in A2:A10 which also appear in another worksheet in Column C. So if there is a blank in cell A2 it means that i WOULD like to keep the rows in the other worksheet and delete all of the data with the non-blank cells in cells A3:A10.

In essence, I want to create a filtering system that is user specified.. Where everything except for the user-specified value is deleted (in this example, cell A2)

 

[Fluff]

You're original code deletes all rows where the value in col C is the same as the value in A2, now you seem to be saying you want to do the opposite.
Which is it?

 

[Alex89]

That is exactly what I'm trying to accomplish.

So, in this case I have a total of 8 publishers represented in A2:A10, each publisher produces multiple pieces of content - each piece of content is shown in the worksheet beside it. Column C has the name of the publisher that produced the content. Column A2:A10 is a list of ALL of the publishers in column C, so by keeping all of the names of the publishers in A2:A10 it would delete all of the rows of data in the next worksheet. By deleting the name of a publisher in A2:A10 (leaving a blank), it would only show the rows of data with that publisher.

For example, if I had a list of 8 publishers and I only wanted the data from National Geographic I would delete the cell in range A2:A10 where it says National Geographic. This would delete all of the rows except national geographic. The problem I am having is creating code to ignore the blank where National Geographic used to be.

 

[Fluff]

Ok, how about

Dim Cl As Range
With rngData
   For Each Cl In enterinfo.Range("A2:A10")
      If Cl.Value <> "" Then
         .AutoFilter Field:=1, Criteria1:=Cl.Value
         'Delete the visible rows while keeping the header
         .Parent.AutoFilter.Range.Offset(1, 0).EntireRow.Delete
      End If
   Next Cl
End With