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?
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)
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
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).
This is the answer:
numCols = objSheet.UsedRange.Columns.count
Source: Stackoverflow.com