PDA

View Full Version : Solved: Find Last used Column



swoozie
05-19-2006, 11:04 AM
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.

austenr
05-19-2006, 12:48 PM
From MWE's KB entry:

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

swoozie
05-19-2006, 01:02 PM
Thank you!

Cyberdude
05-21-2006, 12:44 PM
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.

mdmackillop
05-21-2006, 12:50 PM
Don't forget SpecialCells
LastCol = Cells.SpecialCells(xlCellTypeLastCell).Column
LastRow = Cells.SpecialCells(xlCellTypeLastCell).Row

swoozie
05-22-2006, 05:12 AM
Don't forget SpecialCells
LastCol = Cells.SpecialCells(xlCellTypeLastCell).Column
LastRow = Cells.SpecialCells(xlCellTypeLastCell).Row
FYI, When I did this one (tried first) It gave me 256 as the answer