Filling array after autofilter and sort

[bnj1776]

Hello,

Today I have been searching for a way to load an array with data from a sorted and filtered worksheet and found quite a bit on how it does not work and various workarounds. However, see below for what is working for me. The thing is I am new to Excel VBA so maybe I am creating some kind of problem that that I am just not seeing? Your thoughts on potential problems please.

Thank you,
Brian



The set up...

Dim loCustomers As ListObject

Dim vCustomersHeaders As Variant
Dim vCustomersBody As Variant


Dim row_Customers As Integer
Dim col_lCustomerID As Integer
Dim col_sCustomerNameShort As Integer
Dim col_sStatus As Integer

' Load the customer file - retrive the worksheet into a list object
Set loCustomers = tbl_Customers.ListObjects("tblCustomers")

' Create an array of the table header row
vCustomersHeaders = loCustomers.HeaderRowRange

' Get the column numbers (as the columns may be moved)
col_lCustomerID = GetHeaderColNum("lCustomerID", vCustomersHeaders)
col_sCustomerNameShort = GetHeaderColNum("sCustomerNameShort", vCustomersHeaders)
col_sStatus = GetHeaderColNum("sStatus", vCustomersHeaders)


Then when the user chooses to include or not include "Inactive" customers on the userform...

Private Sub pgCustomers_CheckBox_IncludeInactive_Change()

On Error Resume Next 'continue if no filters found
loCustomers.AutoFilter.ShowAllData 'clear any existing filters from the worksheet
loCustomers.Sort.SortFields.Clear 'clear any existing sorts from the worksheet

If pgCustomers_CheckBox_IncludeInactive = True Then
​

' Incuded Inactive customers
With loCustomers​

.Sort.SortFields.Add Key:=Range("tblCustomers[sCustomerNameShort]"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal​

.Sort.SortFields.Add Key:=Range("tblCustomers[lCustomerID]"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal​

With .Sort
​

.Header = xlYes​

.MatchCase = False​

.Apply​

End With​

End With​

Else
​

' Exclude Inactive customers
With loCustomers
​

With .Range
​

.AutoFilter Field:=col_sStatus, Criteria1:="<>" & sInactive 'hide inactive customers​

End With​

.Sort.SortFields.Add Key:=Range("tblCustomers[sCustomerNameShort]"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.Sort.SortFields.Add Key:=Range("tblCustomers[lCustomerID]"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal​

With .Sort
​

.Header = xlYes
.MatchCase = False
.Apply​

End With​

End With​

End If

' Create an array from the filtered and sorted table body
vCustomersBody = loCustomers.DataBodyRange.SpecialCells(xlCellTypeVisible)

On Error Resume Next 'continue if no filters found
loCustomers.AutoFilter.ShowAllData 'clear the filters from the the worksheet
loCustomers.Sort.SortFields.Clear 'clear any existing sorts from the worksheet

For row_Customers = LBound(vCustomersBody) To UBound(vCustomersBody)​

Debug.Print vCustomersBody(row_Customers, col_lCustomerID) & " " & vCustomersBody(row_Customers, col_sStatus)​

Next row_Customers
MsgBox "test stop"

' set the starting point
row_Customers = 1

' Load the first record
Call pgCustomers_Change_Row
Call pgCustomers_ResetButtons​

End Sub