count the number of columns before the active column

jgarland

New Member
Joined
Nov 6, 2005
Messages
32
Hi all.

I'm trying to find the correct excel VBA code to count the number of columns in a named range, from the start of the range up to and including the current active cell location and return that value to a specific cell.

Example:
"mynamedrange" is columns AA thru AZ and the current active cell is in column AF. The returned number should be 6.

The returned number will be variable as there will be future columns inserted, and the active cell location will vary. I've scoured the web and have not found anything usable.

Ideas & suggestions?
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
7,793
Office Version
365
Platform
Windows
Something like this?

Code:
Set rng = Intersect(Range("mynamedrange"), ActiveCell)
If Not rng Is Nothing Then
    myVal = ActiveCell.Column - Range("mynamedrange").Column + 1
    MsgBox myVal
End If
 
Last edited:

jgarland

New Member
Joined
Nov 6, 2005
Messages
32
Works perfect! Thanks stf.

Can the code be altered to return the value no matter what row the active cell is in?

For instance:
"mynamedrange" is row 5 in columns AA thru AZ, and the current active cell is in AF10, or AF11, ect.
 

Forum statistics

Threads
1,082,510
Messages
5,365,988
Members
400,865
Latest member
LuciaRomo

Some videos you may like

This Week's Hot Topics

Top