Last Column Is 1 More Than Expected

[wsnyder]

Hi all,

Using Excel 365.

I am trying to find the last used column on the worksheet.
However, it is consistently returning 11 where I expect 10.
I highlighted column 11 to right and cleared all.
Still, the code returns 11.
What am I missing?

Thanks,
-w

Sub foo()

    Dim wb As Workbook
    Dim ws As Worksheet
    Dim i As Long
    Dim j As Long
    
    Set wb = Workbooks("rpt.xlsx")
    Set ws = wb.Worksheets(1)
    i = 0
    i = GetLastUsedColumn(ws:=ws)
    j = GetFirstNonBlankCellInColumn(ws:=ws, _
                                     col_number:=i)
    
    Debug.Print "Last used column: "; i
    Debug.Print "First Non-blank row: "; j
    
    Set ws = Nothing
    Set wb = Nothing
    
    
    
End Sub


Public Function GetLastUsedColumn(ws As Worksheet) As Long

    'Purpose    :   Get last used column on a worksheet
    'Parameters :
    '1.)    ws  :   Required parameter. A Worksheet Object.
    '
    '=============================================================================================================================
    '=============================================================================================================================

    GetLastUsedColumn = ws.Range("A1").SpecialCells(xlCellTypeLastCell).Column

End Function

 

[RoryA]

If you press Ctrl+End on the sheet in question, which column is the selected cell in?

 

[Peter_SSs]

I highlighted column 11 to right and cleared all.

Also, make sure that you Save the workbook immediately after doing that.

 

[Marc L]

Hi, for a smart worksheet only (other ways for dumb one) a VBA demonstration :​

Sub Demo1()
        Dim C%
    With Workbooks("rpt.xlsx").Sheets(1).UsedRange.Columns
        C = .Item(.Count).Column
    End With
        MsgBox "Last column is #" & C
End Sub

 

[wsnyder]

If you press Ctrl+End on the sheet in question, which column is the selected cell in?

Column 10

Thanks,
-w

 

[wsnyder]

Also, make sure that you Save the workbook immediately after doing that.

Thanks Peter,
Seems to have solved it.
I'll keep an eye on it.

Thanks,
-w

 

[wsnyder]

Hi, for a smart worksheet only (other ways for dumb one) a VBA demonstration :​

Sub Demo1()
        Dim C%
    With Workbooks("rpt.xlsx").Sheets(1).UsedRange.Columns
        C = .Item(.Count).Column
    End With
        MsgBox "Last column is #" & C
End Sub

Thanks Marc,

What is the c%?
I've never seen that.

Thanks,
-w

 

[Marc L]

It's the character data type declaration as you can check in the VBE Locals windows like in VBA help for pretty each data type …​

 

[Peter_SSs]

Thanks Peter,
Seems to have solved it.

You're welcome. Thanks for the follow-up.