Are you looking to locate the last displayed value in Row 2 or the last cell
in Row 2 with anything in it? That last part refers to formulas that are
currently displaying the empty string ("")... the uncommented Lastcolumn
statement finds the last cell with anything in it (a value OR a formula even
if that formula is displaying the empty string). If you are after the column
with the last displayed value in it (even if there cells after it with
formulas currently displaying empty strings), then use this statement
instead...

Lastcolumn = ActiveSheet.Rows(2).Find(What:="*", LookIn:=xlValues, _
SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

--
Rick (MVP - Excel)


"Sheela" wrote in message
...

I have a macro to find a last column in all the worksheets in a workbook.
In some sheets it is correctly selecting the last column, but in some
sheets
it is passing the last column and selecting some other column on way right
to
it.
I tried to use the following two methods, both are working the same way.

Could someone figure out the correct way to find the last column?

Public Sub test()

Dim ws As Worksheet
Dim Lastrow, Lastcolumn As Long

On Error Resume Next

For Each ws In ActiveWorkbook.Worksheets

With ws

Lastrow = .Range("A" & Rows.Count).End(xlUp).Row
'Lastcolumn = ActiveCell.SpecialCells(xlCellTypeLastCell).Column
Lastcolumn = ActiveSheet.Cells(2, Columns.Count).End(xlToLeft).Column

.Cells(Lastrow, 1).Copy
.Range(.Cells(1, Lastcolumn + 1), .Cells(Lastrow, Lastcolumn +
1)).PasteSpecial Paste:=xlPasteAll, Transpose:=True

End With

Next ws
On Error GoTo 0

End Sub