0

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    |         |
+---------+---------+---------+---------+---------+
Windle
  • 1,385
  • 2
  • 14
  • 33
Lorenzo B.
  • 3
  • 2

3 Answers3

1

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
VBasic2008
  • 44,888
  • 5
  • 17
  • 28
  • Thank you! In the end I had to delete filters and then apply them again, I used the code here: https://stackoverflow.com/questions/9489126/in-excel-vba-how-do-i-save-restore-a-user-defined-filter – Lorenzo B. Oct 14 '20 at 12:55
  • Great! Thanks for the link. Definitely will check it out. – VBasic2008 Oct 14 '20 at 13:33
  • I realized the code I linked only works if the filter is in one column, if you have multiple columns filtered it gives an error. So in the end to count the number of columns I simply counted the number of filtered ones. – Lorenzo B. Oct 15 '20 at 14:31
0

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
Lars Gunnar
  • 1
0

Alternatively, you can try:

findlastcol = Selection.SpecialCells(xlCellTypeLastCell).Column
Seven Up
  • 98
  • 8