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   amp  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

User · Answer

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)

User · Answer

This is the answer   numCols   objSheet UsedRange Columns count   Documentation of the UsedRange property

User · Answer

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

User · Answer

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

User · Answer

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  amp    rows and    amp   Columns Count  amp    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

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

Examples related to vba

Examples related to excel