Select all rows and columns that have data then filter with multiple criteria

Question

How do I select all rows and columns that have data then filter with multiple criteria?

I recorded a macro.

    Sheet2.Activate
    Sheet2.Range("$A$1:$L$85").AutoFilter Field:=12, Criteria1:=Array( _
    "Apples", "Pineapple", "Oranges", "Mango", "Grapes", _
    "Banana", "Papaya"), Operator:=xlFilterValues
    Columns("A:L").Select
    Selection.copy
    Sheets("Food").Select
    Range("A:L").Select
    Sheet7.Paste'

I want to select all the data in A1 to L1. But my code is only for specific number of rows and columns. If the data changes from $A$1:$L$85 and goes more than 85 rows and more than the column L, it will not capture all the data that needs to be filtered.

Maybe look at how to find the last used cell. Then you can get its row and column. — BigBen

L42 L42 · Accepted Answer · 2018-12-21T05:23:08

You can actually use the link provided in the comment, but you can have a go at this.
It seems the UsedRange method somehow improved in the latest Excel Versions (currently using office 365).

Dim lc As Long, lr As Long
Dim r As Range

With Sheet2
  .AutoFilterMode = False
  With .UsedRange
    lr = .Rows.Count
    'lc = .Columns.Count
    lc = 12 '/* your column is bounded to L */
  End With

  Set r = .Range(.Cells(1, 1), .Cells(lr, lc))
  r.AutoFilter Field:=12, Criteria1:=Array( _
               "Apples", "Pineapple", "Oranges", "Mango", "Grapes", _
               "Banana", "Papaya"), Operator:=xlFilterValues

  r.SpecialCells(xlCellTypeVisible).Copy Sheet7.Range("A1")
End With

Select all rows and columns that have data then filter with multiple criteria

1 Answers