VBA filtering as not equal is not working

ghz 8months ago ⋅ 91 views

I'm creating few excel files. After that I want to filter range that are not equal to specific data. Filtered range needs to be deleted - so I want to keep only data that are equal to FileName.

I'm using solution as below

Criteria1:="<>" & FileName

But VBA seems not to recognize FileName and after filtering I see all data

Although if I use

Criteria1:=FileName

Then I receive range correctly. What I do wrong?

Sub FilterAndDeleteRows()
    Dim wsBoMInput As Worksheet
    Dim FileName As String
    Dim lastRow As Long
    Dim rngToDelete As Range
    Dim destFolder As String
    Dim destFile As String
    Dim wbDest As Workbook

    ' Set the destination folder path
    destFolder = ThisWorkbook.Path & "\Files\"

    ' Loop through each file in the destination folder
    destFile = Dir(destFolder & "*.xlsm")
    Do While destFile <> ""
        ' Open the destination workbook
        Set wbDest = Workbooks.Open(destFolder & destFile)
        ' Set the destination worksheet (adjust the sheet name if needed)
        Set wsBoMInput = wbDest.Worksheets("BoM Input")
        ' Get the file name from the "ME List" worksheet
        FileName = wbDest.Worksheets("ME List").Range("C1").value

        ' Find the last row in column G
        lastRow = wsBoMInput.Cells(wsBoMInput.Rows.Count, "G").End(xlUp).Row

        ' Filter out rows where column G's value is not equal to FileName
        wsBoMInput.Range("A5:DT" & lastRow).AutoFilter Field:=7, Criteria1:="<>" & FileName
        
        ' Check if any rows are visible after filtering
        If wsBoMInput.AutoFilter.Range.Offset(1, 0).SpecialCells(xlCellTypeVisible).Count > 1 Then
            ' Set the range to delete (excluding header row)
            Set rngToDelete = wsBoMInput.AutoFilter.Range.Offset(1, 0).SpecialCells(xlCellTypeVisible)
            ' Delete the rows
            rngToDelete.EntireRow.Delete
        End If

        ' Clear the filter
        wsBoMInput.AutoFilterMode = False

        ' Close and save the destination workbook
        wbDest.Close SaveChanges:=True

        ' Get the next file in the folder
        destFile = Dir
    Loop
    MsgBox "Done!"
End Sub

Answers

The issue you're encountering with the VBA code is likely due to the fact that the FileName variable is not being properly concatenated with the filter criteria string. In VBA, when you use double quotes within a string to specify text, you need to concatenate the variable with the rest of the string using the & operator.

Here's how you can fix it:

' Filter out rows where column G's value is not equal to FileName
wsBoMInput.Range("A5:DT" & lastRow).AutoFilter Field:=7, Criteria1:="<>" & FileName

By concatenating the FileName variable with the filter criteria string using &, you ensure that the correct criteria is applied to the filter.

With this change, the filter should work as expected, filtering out rows where the value in column G is not equal to the value of FileName.

#excel