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

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]44[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]55[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]66[/TD]
[TD]1[/TD]
[TD]44[/TD]
[/TR]
[TR]
[TD]test[/TD]
[TD][/TD]
[TD]44[/TD]
[/TR]
</tbody>[/TABLE]

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