finding last column not always working correctly

[Sheela]

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

 

[ryguy7272]

You must have some formatting or some kind of 'invisible' data in those
columns that get passed over. In one of those troublesome sheets, click a
cell, and hit Ctrl+End on the keyboard. Do you go beyond the column that you
expect to stop at? If so, click on the letter to the right of the last
column you want to stop at, Ctrl+Shift+RightArrowKey, then right-click and
click Delete. Save the workbook, hit Ctrl+End, now you should stop at the
column that you expect to stop at.

Give it a try and post back with your findings.

HTH,
Ryan---

 

[Gary''s Student]

This should find the last column in each sheet:

Sub FindLastColumn()
Dim ncl As Long
ncl = Columns.Count
Dim ws As Worksheet
For Each ws In Worksheets
With ws
For i = ncl To 1 Step -1
If Application.WorksheetFunction.CountA(.Columns(i)) <> 0 Then
MsgBox (ws.Name & " column " & i & " is last")
GoTo nextsheet
End If
Next
MsgBox ("Worksheet is empty")
nextsheet:
End With
Next
End Sub

 

[Rick Rothstein]

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

 

[Sheela]

Thank you all for your responses.

Ryan: I tried to see whether there are any hidden characters or anything
with the Ctrl+End, there were no hidden characters. the cursor always stopped
at the last column.

Rick, I used your function and realized that it calculates the lastcolumn in
the first sheet, and always using the same number as lastcolumn in all other
sheets.

somehow the Lastcolumn is not being calculated in each sheet.
Could you see my code in the original post and advise me ?

thank you very much in advance,
Sheela

 

[Rick Rothstein]

Here is your original code, modified to use the statement I posted
originally (notice, the only change I made was to remove the ActiveSheet
reference and replace it with ws which is the variable name you used to
iterate through the worksheets).

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 = ws.Rows(2).Find(What:="*", LookIn:=xlValues, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).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

 

[Sheela]

Thank you very much, Rick.

It is working the way it is expected. but I need to change my code to find
the last column, not just in row 2. Is there a way to find the last column
in a worksheet in any row?

Thank you again.

Sheela

 

[Sheela]

I am sorry I posted it to soon. I took out the rownumber (2) then it is
working perfect.

Have a wonderful day.
Sheela.

 

[Rick Rothstein]

Try this code instead...

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 = ws.Cells.Find(What:="*", SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, LookIn:=xlValues).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

 

[Sheela]

Thank you very much.

Try this code instead...

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 = ws.Cells.Find(What:="*", SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, LookIn:=xlValues).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