Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 844
  • Last Modified:

Excel VBA Find last column in range

Ugh!!

Hell Experts,

I need help to find the last used cell column number in a set range.
My range is "B4:H4".  As long as there is something in one of those cells - that is my column number I need.

I have data in A4 & I4 so I can not use the typical examples I found on-line.

Once I find the column number - I can finish my code.

Any assistance would be great,
Thanks
0
mike637
Asked:
mike637
  • 2
1 Solution
 
etech0Commented:
Will the range always be B4:H4?
0
 
mike637Author Commented:
Yes it will.
0
 
andrewssd3Commented:
Try this function:
Option Explicit

Public Sub Test()

    Dim s As Worksheet
    Dim myRange As Range
    
    Set s = ActiveSheet
    Set myRange = s.Range("B4:H4")

    Debug.Print GetLastColNum(myRange)

End Sub

Public Function GetLastColNum(ByRef rngToCheck As Range) As Long

    Dim i As Long
    
    ' return 0 if nothing is found
    GetLastColNum = 0
    
    For i = rngToCheck.Cells.Count To 1 Step -1
        If Not IsEmpty(rngToCheck.Cells(i).Value) Then
            GetLastColNum = rngToCheck.Cells(i).Column
            Exit For
        End If
    Next i

End Function

Open in new window

0
 
mike637Author Commented:
Thank you Andrew!

I was about to give up and go get a drink. :)

Also - thanks for taking this on a Sunday - you are awesome.

Michael
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now