How to filter and then print an array with VBA?

Question

I usually create a 'virtual' array of the table on the sheet as below and use that array to do the editing. Right now, I have an array and I want to filter it based on the first column. I also want to clear the filter after printing the filtered array. Because I will be doing this more than one time.

ReDim TableArray(LastRow, LastCol) As Variant
For i = 1 To LastRow
    For k = 1 To LastCol
        TableArray(i, k) = Workbooks(wb).Sheets("List").Cells(i, k).Value
    Next k
Next i

I added an example table. Here is my goal:

Filter this table (TableArray) for AA supplier.
Then print it to the 'AA' sheet.
After printing, clear the filter on 'TableArray'.

I searched this but the examples are usually only for 1D tables. I found this code for filtering tables but couldn't adapt it to my problem:

TableArray(lastrow,lastcol).AutoFilter field:=1, Criteria1:="AA"

In the code TableArray(lastrow,lastcol).AutoFilter...: i suspect TableArray is a range and not an array. You could do something similar. Apply the filter on your sheet and then copy the displayed range — Zac
– Zac, Commented Jul 8, 2020 at 14:41

Super Symmetry · Accepted Answer · 2020-07-08 14:36:07Z

You cannot apply autofilter to arrays in memory. Autofilters and sorts can only be applied to excel ranges and in my experience they are far more efficient than any algorithm you can build within VBA.

Try this.

Sub ExtractData(sSheetName As String)
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  
  Dim rngToFilter As Range
  Dim wsDestination As Worksheet
  Dim LastCol As Long
  Dim LastRow As Long
  
  Dim wb As String ' or As Long if you are looping
  wb = "NameOfWorkbook"
  
  With Workbooks(wb)
    With .Sheets("List")
      LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
      LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
      Set rngToFilter = .Range("A1").Resize(LastRow, LastCol)
    End With
    Set wsDestination = .Sheets(sSheetName)
  End With
  
  With rngToFilter
    .AutoFilter Field:=1, Criteria1:=sSheetName
    
    wsDestination.Cells.ClearContents
    .Copy Destination:=wsDestination.Cells(1, 1)
    
    If Not ActiveSheet Is .Parent Then .Parent.Activate
    If Intersect(ActiveCell, .Cells) Is Nothing Then .Cells(1, 1).Select
    With .Parent
      If .FilterMode Then .ShowAllData
    End With
  End With
  
  Application.EnableEvents = True
  Application.ScreenUpdating = True
End Sub

Sub ExtractAAData()
  ExtractData sSheetName:="AA"
End Sub

Side note:

The simplest way to generate you virtual array is like this:

Dim TableArray as Variant
TableArray = Workbooks(wb).Sheets("List").Cells(1, 1).Resize(LastRow, LastCol).Value

Collectives™ on Stack Overflow

How to filter and then print an array with VBA?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related