Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Excel VBA Find last column in range

Posted on 2013-01-06
4
Medium Priority
?
840 Views
Last Modified: 2013-01-06
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
Comment
Question by:mike637
  • 2
4 Comments
 
LVL 10

Expert Comment

by:etech0
ID: 38749106
Will the range always be B4:H4?
0
 

Author Comment

by:mike637
ID: 38749109
Yes it will.
0
 
LVL 17

Accepted Solution

by:
andrewssd3 earned 2000 total points
ID: 38749115
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
 

Author Closing Comment

by:mike637
ID: 38749137
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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question