26

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?

Jean-François Corbett
  • 37,420
  • 30
  • 139
  • 188
niko
  • 9,285
  • 27
  • 84
  • 131
  • @niko Its not clear whether you wanted the number of used columns (question title), or the position of the last used column (implied by your `row` code)? – brettdj Dec 28 '13 at 06:13
  • @niko, a better method of finding the actual used portion of the sheet rather than the `xlUp` variants, or kludgy `UsedRange` is `Find`. see http://stackoverflow.com/questions/8283797/select-range-in-excel-vba/8283941#8283941 – brettdj Dec 28 '13 at 06:17

5 Answers5

50

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.

Jean-François Corbett
  • 37,420
  • 30
  • 139
  • 188
  • Hi. I tried to use the code `Dim lastRow As Long lastRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row MsgBox lastRow` but VBA says "Runtime error 424: Object required" at the second of those lines. Now I am lost. Which function exactly does require an object and what did it get instead? – mzuba Jun 27 '16 at 14:49
  • 2
    Do you actually have a sheet codenamed `Sheet1`? If not, then that's one thing that will cause that error. – Jean-François Corbett Jun 28 '16 at 07:47
8

Jean-François Corbett's answer is perfect. To be exhaustive I would just like to add that with some restrictons you could also use UsedRange.Columns.Count or UsedRange.Rows.Count.
The problem is that UsedRange is not always updated when deleting rows/columns (at least until you reopen the workbook).

iDevlop
  • 24,841
  • 11
  • 90
  • 149
2

It's possible you forgot a sheet1 each time somewhere before the columns.count, or it will count the activesheet columns and not the sheet1's.

Also, shouldn't it be xltoleft instead of xltoright? (Ok it is very late here, but I think I know my right from left) I checked it, you must write xltoleft.

lastColumn = Sheet1.Cells(1, sheet1.Columns.Count).End(xlToleft).Column
seaotternerd
  • 6,298
  • 2
  • 47
  • 58
Patrick Lepelletier
  • 1,596
  • 2
  • 17
  • 24
2

This is the answer:

numCols = objSheet.UsedRange.Columns.count

Documentation of the UsedRange property

moggi
  • 1,466
  • 4
  • 18
  • 29
Noel Cole
  • 21
  • 2
0

Result is shown in the following code as column number (8,9 etc.):

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

Result is shown in the following code as letter (H,I etc.):

Dim lastColumn As Long
lastColumn = Sheet1.Cells(1, Columns.Count).End(xlToLeft).Column
MsgBox Split(Sheet1.Cells(1, lastColumn).Address, "$")(1)
Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
kadrleyn
  • 364
  • 1
  • 5