Solved

Excel VBA Find last column in range

Posted on 2013-01-06
4
824 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 500 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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

746 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now