Criteria1:="="

[Legacy 198477]

I am trying to filter out rows where specific column data is blank. When I run the code below and there are no rows with blank data, the entire worksheet is copied. If I do have one row with blank data in the column, only that row is copied. I need to script for all scenarios, so there will be times when I have rows with no blank columns. In this case, I do not want to copy anything. Help is greatly appreciated!

Sub NoInventoryDate()

Dim Rng As Range


With Sheets("Inventoried Items")
.AutoFilterMode = False
.Range("A1").AutoFilter field:=6, Criteria1:="="
Set Rng = .AutoFilter.Range
Set Rng = Rng.Offset(1).Resize(Rng.Rows.count - 1)
Rng.Copy Sheets("Missing").Cells(Rows.count, 1).End(xlUp)(2)
Rng.EntireRow.Delete
.AutoFilterMode = False
End With


End Sub

 

[mikerickson]

Does Criteria1:="<>" do what you want?

 

[Legacy 198477]

No. Unfortunately, this copies everything over as well. I am looking for blanks in a specific column in a row. If I find this, I want to copy the row to another worksheet. I'm finding that if none of my rows has blank data in this column, the Criteria1:="=" just copies everything over. If I have one row, for example that has blank data in the column, the Criteria1:="=" works for that one row. If not all the rows are copied.

 

[Marcelo Branco]

Maybe something like
(try it in a copy of your workbook)

Sub filterTest()
    Dim Rng As Range
 
    With Sheets("Inventoried Items")
 
        .AutoFilterMode = False
        .Range("A1").AutoFilter field:=6, Criteria1:="="
 
        On Error Resume Next
        Set Rng = .AutoFilter.Range.Offset(1). _
            Resize(.AutoFilter.Range.Rows.Count - 1). _
            SpecialCells(xlCellTypeVisible)
 
        If Not Rng Is Nothing Then
            Rng.Copy Sheets("Missing").Cells(Rows.Count, 1) _
            .End(xlUp)(2)
            Rng.EntireRow.Delete
        End If
 
        .AutoFilterMode = False
 
    End With
End Sub

M.

 

[Legacy 198477]

This works great!! Thanks!

 

[Marcelo Branco]

You are welcome! Thanks for the feedback.