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

Question

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)?

BigBen · Accepted Answer · 2021-11-15 17:01:29Z

1

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.

edited Nov 15, 2021 at 17:01

answered Nov 15, 2021 at 16:55

BigBen

50.4k7 gold badges29 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to count the number of columns in Excel table using 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