True last column in VBA

Question

So i have this working but some days the data in Column P is blank then the "Last Column" stops at P when actually the last column could be S or T or what ever. Here is what i have so long:

Sub trledSCB()

    ActiveSheet.ListObjects("MItrled").Range.AutoFilter Field:=1

    ActiveSheet.ListObjects("MItrled").Range.AutoFilter Field:=1, Criteria1:= _
        "836553196"


        lastCol = ActiveSheet.Range("b4").End(xlToRight).Column
    Lastrow = ActiveSheet.Cells(4, 1).End(xlDown).Row
    ActiveSheet.Range("b4", ActiveSheet.Cells(Lastrow, lastCol)).Select

End Sub

I hope you guys can help me here?

score 3 · Answer 1 · answered Nov 20 '18 at 12:10

The problem seems to be how you are selecting your last column.

How you need to change it depends on what you want to count as your Last Column.

Do you want the last column within the Used range?
Even if that column only contains blanks?
Do you want the last column which contains a value within a specific row?

Try this:

Dim LastCell As Range

Set LastCell = ActiveSheet.Cells.Find(What:="*", _
                                      After:=ActiveSheet.Cells(1, 1), _ 
                                      LookIn:=xlFormulas, _
                                      LookAt:= xlPart, _ 
                                      SearchOrder:=xlByColumns, _ 
                                      SearchDirection:=xlPrevious, _ 
                                      MatchCase:=False _
                                     )
lastCol = LastCell.Column

Pawel Czyz · Accepted Answer · 2018-11-20T12:15:21.567

You can try this:

LastColumn = sht.Cells(7, sht.Columns.Count).End(xlToLeft).Column

More about it here: https://www.thespreadsheetguru.com/blog/2014/7/7/5-different-ways-to-find-the-last-row-or-last-column-using-vba

This basically does same what your original line does but starting from right to left.

Sub trledSCB()

    ActiveSheet.ListObjects("MItrled").Range.AutoFilter Field:=1

    ActiveSheet.ListObjects("MItrled").Range.AutoFilter Field:=1, Criteria1:= _
        "836553196"


        lastCol = ActiveSheet.Cells(7, ActiveSheet.Columns.Count).End(xlToLeft).Column
    Lastrow = ActiveSheet.Cells(4, 2).End(xlDown).Row
    ActiveSheet.Range("b4", ActiveSheet.Cells(Lastrow, lastCol)).Select

End Sub

By the way, using ActiveSheet is bad practice. Check this: How to avoid using Select in Excel VBA

True last column in VBA

2 Answers2