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 | | +---------+---------+---------+---------+---------+
Improve this question

3 Answers 3

Reset to default
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
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thank you! In the end I had to delete filters and then apply them again, I used the code here: stackoverflow.com/questions/9489126/…
Great! Thanks for the link. Definitely will check it out.
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.
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
Improve this answer

1 Comment

Thank you for the tip, but... Be careful, this method considers also cleansed cells :)
0

Alternatively, you can try:

findlastcol = Selection.SpecialCells(xlCellTypeLastCell).Column
Improve this answer

1 Comment

Thank you for the tip, but... Be careful, this method considers also cleansed cells :)

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.