Snasui Excel Forum

Code: Select all

arrList(lngCnt) = cell.Text

Code: Select all

arrList(lngCnt) = cell.Value

Code: Select all

...  .ShowAllData
End With
    FilterMode = False
    AutoFilterMode = False

Code: Select all

Sub Filter()

Dim RngOne As Range, cell As Range
Dim LastColumn, LastCell   As Long
Dim arrList() As String, lngCnt  As Long
Dim Sheet1 As String


With Sheets("Sheet1")

    LastCell = .Range("d" & Sheets("Sheet1").Rows.Count).End(xlUp).Row
    Set RngOne = .Range("d2:d" & LastCell)

End With

lngCnt = 0
For Each cell In RngOne
    ReDim Preserve arrList(lngCnt)
    arrList(lngCnt) = cell.Text
    lngCnt = lngCnt1 + 1
Next

With Sheets("Sheet1")

    LastColumn = .Range("A1").CurrentRegion.Columns.Count
    
    If .FilterMode Then .ShowAllData

    .Range("A1" & LastColumn).AutoFilter Field:=1, Criteria1:=arrList, Operator:=xlFilterValues
    .Range("A1").CurrentRegion.Offset(0, 0).SpecialCells(xlCellTypeVisible).Copy
     Sheets("Sheet2").Range("A1").PasteSpecial Paste:=xlPasteAll
    .ShowAllData
End With

    FilterMode = False
    AutoFilterMode = False

End Sub

Code: Select all

'Other code
     Sheets("Sheet2").Range("A1").PasteSpecial Paste:=xlPasteAll
    .ShowAllData
    .AutoFilterMode = False
End With

End Sub