Finding the number of non-blank columns in an Excel sheet using VBA

Question

How do I find the number of used columns in an Excel sheet using VBA?

Dim lastRow As Long lastRow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row MsgBox lastRow 

Using the above VBA I'm able to find the number of rows. But how do I find the number of columns in my given excel file?

Answer 1

Your example code gets the row number of the last non-blank cell in the current column, and can be rewritten as follows:

Dim lastRow As Long lastRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row MsgBox lastRow 

It is then easy to see that the equivalent code to get the column number of the last non-blank cell in the current row is:

Dim lastColumn As Long lastColumn = Sheet1.Cells(1, Columns.Count).End(xlToLeft).Column MsgBox lastColumn 

This may also be of use to you:

With Sheet1.UsedRange     MsgBox .Rows.Count & " rows and " & .Columns.Count & " columns" End With 

but be aware that if column A and/or row 1 are blank, then this will not yield the same result as the other examples above. For more, read up on the UsedRange property.