CODEWITHSUNDEEP
excelvbascripting
Lorenzo B.
Lorenzo B.

Reputation: 3

VBA, find last used column in the whole sheet

I googled a lot and found a lot of different solutions, but I need to improve the one I'm using now.

I want to find the last used column in the sheet using the find method not to consider the deleted cells.

All I want is to get the last column used, including the one in the row of the starting cell. In the image below if I use my code it will give last column = 4, because in the 2nd row data stops at column 4. Why isn't it giving 5 (header column) as result?

Thank you!!

With ActiveSheet
If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
    findlastcol = .Cells.Find(What:="*", _
                  After:=.Range("A1"), _
                  LookAt:=xlPart, _
                  LookIn:=xlFormulas, _
                  SearchOrder:=xlByColumns, _
                  SearchDirection:=xlPrevious, _
                  MatchCase:=False).Column
Else
    findlastcol = 1
End If
End With

Example Table screenshot

+---------+---------+---------+---------+---------+
| Header1 | Header2 | Header3 | Header4 | Header5 |
+---------+---------+---------+---------+---------+
| Data    | Data    | Data    | Data    |         |
+---------+---------+---------+---------+---------+

Upvotes: 0

Views: 259

Answers (3)

VBasic2008
VBasic2008

Reputation: 54807

AutoFilter Kicks the Find Method

  • The Find method with xlFormulas is pretty much 'bullet proof', unless there is a filter involved which is happening in your case.
  • The following example shows how to do it by turning the AutoFilter off, which is not quite what one wants. It also shows how there were three not needed arguments. Additionally it is a different approach which does not need CountA.
  • A proper solution would be to copy the current filter into a Filter object and then apply it later back. Here is an example of how to do it.

The Code

Sub testBulletProof()
    Dim LastCol As Long
    Dim rng As Range
    With ActiveSheet
        If .AutoFilterMode Then
            .AutoFilterMode = False
        End If
        Set rng = .Cells.Find(What:="*", _
                              LookIn:=xlFormulas, _
                              SearchOrder:=xlByColumns, _
                              SearchDirection:=xlPrevious)
    End With
    If Not rng Is Nothing Then
        LastCol = rng.Column
    Else
        LastCol = 1
    End If
    Debug.Print LastCol
End Sub
  • Since you might know the row where the headers are and the data will not have more columns then the header does, you could use this:

The Code

Sub testFindInRow()
    Dim LastCol As Long
    Dim rng As Range
    With ActiveSheet
        Set rng = .Rows(1).Find(What:="*", _
                                LookIn:=xlFormulas, _
                                SearchDirection:=xlPrevious)
    End With
    If Not rng Is Nothing Then
        LastCol = rng.Column
    Else
        LastCol = 1
    End If
    Debug.Print LastCol
End Sub

Upvotes: 1

Seven Up
Seven Up

Reputation: 98

Alternatively, you can try:

findlastcol = Selection.SpecialCells(xlCellTypeLastCell).Column

Upvotes: 0

Lars Gunnar
Lars Gunnar

Reputation: 1

You could try the following code:

Sub FindLastColumn()

Dim iLastCol As Integer

ActiveSheet.UsedRange 'Refreshing used range (may need to save wb also)

iLastCol = ActiveSheet.UsedRange.Columns(ActiveSheet.UsedRange.Columns.Count).Column


End Sub

Upvotes: 0

Related Questions