I am looking for a way to find the last used column so I am able to insert text into the next empty column to the right.
I am looking for a way to find the last used column so I am able to insert text into the next empty column to the right.
From MWE's KB entry:
[VBA] Function xlLastCol(Optional WorksheetName As String) As Long
' finds the last populated col in a worksheet
If WorksheetName = vbNullString Then WorksheetName = ActiveSheet.Name
With Worksheets(WorksheetName)
On Error Resume Next
xlLastCol = .Cells.Find("*", .Cells(1), xlFormulas, _
xlWhole, xlByColumns, xlPrevious).Column
If Err <> 0 Then xlLastCol = 0
End With
End Function
[/VBA]
Peace of mind is found in some of the strangest places.
Thank you!
Here's an oversimplified technique that assumes the last column has a value in the row (CurrRow) that is searched:
Range("IV" & CurrRow).End(xlToLeft).Offset(0, 1).Select
Not elegant, but is useful sometimes.
Don't forget SpecialCells
LastCol = Cells.SpecialCells(xlCellTypeLastCell).Column
LastRow = Cells.SpecialCells(xlCellTypeLastCell).Row
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
FYI, When I did this one (tried first) It gave me 256 as the answerOriginally Posted by mdmackillop
Posting Permissions
Reply With Quote
