How to count the number of columns in Excel table using VBA?-CodePudding

Dim housetypes, elements, NumRowsElements, NumColumnsHousetypes

elements = Range("c10:c19")
housetypes = Range("d9:n9")

NumRowsElements = UBound(elements)
NumColumnsHousetypes = UBound(housetypes)

MsgBox ("NumRowsElements is " & NumRowsElements & " NumColumnsHousetypes is " & NumColumnsHousetypes)

I'm not sure why the above code will show me the correct number of row headers (C10 to C19) but not return the correct number of column headers (D9 to N9)?

CodePudding user response：

Both elements and housetypes are 2D arrays, not 1D. The first dimension corresponds to the rows, and the second corresponds to columns.

When using Ubound, if dimension (2nd parameter) is omitted, 1 is assumed.

NumRowsElements = UBound(elements, 1)
NumColumnsHousetypes = UBound(housetypes, 2)

will return the results you want.