How do I find the number of used columns in an Excel sheet using VBA?
开发者_StackOverflowDim 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?
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'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).
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
This is the answer:
numCols = objSheet.UsedRange.Columns.count
Documentation of the UsedRange property
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)
精彩评论