find the last none empty column

[lezawang]

Hi
The code below does not work on case like below when i have first few cells are empty for col 2 and 3. How can I fix that

My case

1
44
55
66	1	44
test		44

<tbody>
</tbody>

the code below will say the last none empty column is #1

https://www.excelcampus.com/vba/find-last-row-column-cell/


Sub Range_End_Method()
'Finds the last non-blank cell in a single row or column

Dim lRow As Long
Dim lCol As Long

'Find the last non-blank cell in column A(1)
lRow = Cells(Rows.Count, 1).End(xlUp).Row

'Find the last non-blank cell in row 1
lCol = Cells(1, Columns.Count).End(xlToLeft).Column

MsgBox "Last Row: " & lRow & vbNewLine & _
"Last Column: " & lCol

End Sub

 

[fhqwgads]

Sub lastcol()
MsgBox ActiveSheet.UsedRange.Columns.Count
End Sub

 

[Akuini]

Another way:

[FONT=Courier]lCol [/FONT]= Cells.Find("*", Cells(1, 1), , , xlByColumns, xlPrevious).Column