2

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.

Community
  • 1
  • 1
N3tt3
  • 21
  • 1
  • Maybe look at [how to find the last used cell](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba). Then you can get its row and column. – BigBen Dec 21 '18 at 05:14

1 Answers1

0

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
L42
  • 19,427
  • 11
  • 44
  • 68
  • If `UsedRange` begins in `A1` and ends in `lr, lc`, wouldn't you just filter the `UsedRange`? – BigBen Dec 21 '18 at 06:12
  • @BigBen Yeah. Op's column is bounded to 12. And you cannot change `UsedRange` for other purpose. So I thought of just using it to get `lr`. But yeah you can do that. – L42 Dec 22 '18 at 01:44