Row and Column Count for a lookup array

[Kurt]

Can someone show me the best way to do a Row and Column count for this lookup array?

[ ReDim aTrades(1 To 4, 1 To 2) As Variant
ReDim aSystemCodes(1 To 69, 1 To 2)
'todo: do a row and column count of the above lookup arrays and use variables
/]

 

[Kurt]

My bad I need to give some more info.

This is the fill value arrays:

'fill values arrays
aRawData = Sheet5.Range("A2:S" & rdLastRow).Value
aTrades = EstShtCodes1.Range("TradeLookup").Value
aSystemCodes = EstShtCodes1.Range("SystemCodeLookup").Value

The EstShtCodes1. Range contains the following values:

ESTmep
Trade Trade
Mechanical Piping MECH
Medical Gas PLBG
Plumbing PLBG
Process Piping MECH

These values are on a worksheet named EstShtCodes in cells K32-L35

These are the SystemCodeLookup values and they are on the same worksheet EstShtCodes in cells H3:L71

Chilled Water Return CHA
Chilled Water Return UG CHU
Chilled Water Supply CHA
Chilled Water Supply UG CHU
Compressed Air MPS
Condensate Drain CDA
Condenser Water Return CWA
Condenser Water Return UG CWU
Condenser Water Supply CWA
Condenser Water Supply UG CWU
Equalizer CWA
Filter Water Return MIP
Filter Water Supply MIP
Heating Water Return HWA
Heating Water Return UG HWU
Heating Water Supply HWA
Heating Water Supply UG HWU
Industrial Water Return MIP
Industrial Water Supply MIP
Make-Up Water DWA
Mechanical Vent MIP
Natural Gas NGA
Natural Gas UG NGU
Natural Gas Vent NGA
Refrigerant Gas REF
Refrigerant Liquid REF
Steam Condensate - HP STM
Steam Condensate - LP STM
Steam Condensate - MP STM
Steam HP STM
Steam LP STM
Steam MP STM
Carbon Dioxide MED
Hydrogen MED
Medical Air MED
Medical Vacuum MED
Nitrogen MED
Nitrous Oxide MED
Oxygen MED
Oxygen Evacuation MED
Waste Anesthesia Gas Disposal MED
Acid Waste MPS
Deionized Water MPS
Domestic 140° Hot Water DWA
Domestic Cold Water DWA
Domestic Cold Water UG DWU
Domestic Hot Water DWA
Domestic Hot Water Return DWA
Domestic Hot Water UG DWU
Domestic Tempered Water DWA
Grease Waste GWA
Grease Waste UG GWU
Non-Potable Water DWA
Sanitary Vent SWA
Sanitary Waste SWA
Sanitary Waste UG SWU
Storm Drain SDA
Storm Drain Overflow SDA
Storm Drain UG SDU
Sub-Soil Drain UG SDU
Sump Pump Discharge MPS
Acetylene MIP
Argon MIP
Carbon Dioxide MIP
Helium MIP
Hydrogen MIP
Instrument Air MIP
Nitrogen MIP
Vacuum MIP

Sorry I didn't have enough coffee to think this through when I posted earlier!

 

[Fluff]

If you just want to find the size of the array, try something like

   Dim ardRw As Long, ardCol As Long
   Dim aRawData As Variant
   aRawData = Sheet5.Range("A2:S" & rdLastRow).Value
   ardRw = UBound(aRawData)
   ardCol = UBound(aRawData, 2)