Here's something which might be useful. Selecting the entire column based on a row containing data, in this case i am using 5th row:
Dim lColumn As Long
lColumn = ActiveSheet.Cells(5, Columns.Count).End(xlToLeft).Column
MsgBox ("The last used column is: " & lColumn)
Try using the code after you active the sheet:
Dim J as integer
J = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
If you use Cells.SpecialCells(xlCellTypeLastCell).Row
only, the problem will be that the xlCellTypeLastCell
information will not be updated unless one do a "Save file" action. But use UsedRange
will always update the information in realtime.
I know this is old, but I've tested this in many ways and it hasn't let me down yet, unless someone can tell me otherwise.
Row number
Row = ws.Cells.Find(What:="*", After:=[A1] , SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Column Letter
ColumnLetter = Split(ws.Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Cells.Address(1, 0), "$")(0)
Column Number
ColumnNumber = ws.Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
I think we can modify the UsedRange
code from @Readify's answer above to get the last used column even if the starting columns are blank or not.
So this lColumn = ws.UsedRange.Columns.Count
modified to
this lColumn = ws.UsedRange.Column + ws.UsedRange.Columns.Count - 1
will give reliable results always
?Sheet1.UsedRange.Column + Sheet1.UsedRange.Columns.Count - 1
Above line Yields 9
in the immediate window.
Source: Stackoverflow.com