Menu
I've found this method for finding the last data containing row in a sheet:
ws.Range("A65536").End(xlUp).row Is there a similar method for finding the last data containing column in a sheet?
668
Locate the last cell that contains data or formatting on a worksheet. To locate the last cell that contains data or formatting, click anywhere in the worksheet, and then press CTRL+END.
1. Select the Last Used Cell. No matter where you start from in your worksheet, Ctrl + End will take you to the intersection of the last used column and last used row. Sometimes, when you use this shortcut, Excel will move your selection so that is farther to the right or farther down than the data range you can see.
Ctrl-Down -- The shortcut moves the cursor to the last row with data before the first blank row that is encountered; this may be the last row in the table ideally, but only if there are not any blank rows in the table. Ctrl-Up -- The shortcut moves the cursor to the first row with data before a blank row.
Lots of ways to do this. The most reliable is find.
Dim rLastCell As Range Set rLastCell = ws.Cells.Find(What:="*", After:=ws.Cells(1, 1), LookIn:=xlFormulas, LookAt:= _ xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False) MsgBox ("The last used column is: " & rLastCell.Column) If you want to find the last column used in a particular row you can use:
Dim lColumn As Long lColumn = ws.Cells(1, Columns.Count).End(xlToLeft).Column Using used range (less reliable):
Dim lColumn As Long lColumn = ws.UsedRange.Columns.Count Using used range wont work if you have no data in column A. See here for another issue with used range:
See Here regarding resetting used range.
197
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
