Find last column with data

P

poppy

Hi Experts

I am trying to find the last column with data in my sheet. I tried thi
code, but it is giving me errors. Do you have any ideas what I'm doin
wrong?

lastcol = Cells(Columns.Count, "2").End(xlLeft).Column

Kind Regard
 
N

Norman Jones

Hi Poppy,
lastcol = Cells(Columns.Count, "2").End(xlLeft).Column
Click to expand...

If you use a numeric column reference, drop the quotes.

The Cells syntaax should be:

Cells(row, column)

To find the last populated cell in row 2, try amending your code to:

Set lastcol = Cells(2, Columns.Count).End(xlToLeft)


To return the number of the find the last populated column in row 2, try:

Cells(2, Columns.Count).End(xlToLeft).Column


To find the last populated column on the worksheet, try the following
function:

Function LastCol(sh As Worksheet)
On Error Resume Next
LastCol = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
End Function
 
Top