How to use the column of a named range

[Upex]

Hi All,

I'm trying to replace a quoted column reference with reference to the column of a named range so if the column position changes it doesn't need the code updated, but cannot get anything I've tried to work...

I currently use:
sColumn = "J" (with sColumn dim as string)

and then use that later as part of variable ranges... Range(Range(sColumn & 3), Range(sColumn & Sheet1.Rows.Count).End(xlUp)) fashion.

Column J:J is named 'detail' so I'm trying to use something like:
sColumn = range("detail").column

but cannot get anything I've tried to work. Any ideas?

Thanks in advance!

 

[Joe4]

Note that:

range("detail").column

will return the column INDEX (numeric) of the column, NOT the column letter!
So, if "detail" was column J, that expression above would return 10, not "J", as column J is the 10th column (J is the 10th letter).

Then, if referencing your ranges, instead of using RANGE, which requires column references to be letters, you can use CELLS, in which column references can be numeric or letters.
The format of CELLS is:
Cells(row, column)

So if you wanted to return the range reference for "sColumn" in the 3rd row, that would look like:

Cells(3, sColumn)

 

[RoryA]

Note that sColumn should now be declared as Long, not as String.

 

[Joe4]

Note that sColumn should now be declared as Long, not as String.

Excellent point, Rory!

 

[Akuini]

The format of CELLS is:
Cells(column, row)

I think it should be:
Cells(row, column)
Example:
Debug.Print Cells(1, 5).Address 'returns $E$1

 

[Joe4]

I think it should be:
Cells(row, column)
Example:
Debug.Print Cells(1, 5).Address 'returns $E$1

You are 100% correct (apparently, I was suffering from some Excel-dyslexia this morning)!
I went back and edited my previous post to reflect correct information.

Thanks for catching that!

 

[Akuini]

@Joe4
You're welcome.

 

[Upex]

Thank you all, that makes a bit of sense, I think.

But how would I use 'cells' to get just the column ("J" until it moves)?

sColumn =
Cells("detail").Column
Cells(0, "detail").column
Cells(1,"detail").column
Cells(1, "detail")
Cells(1, "detail").address.column

all give me mismatch errors (declared as Long)

 

[Joe4]

Combine what you had before:

Dim sColumn as Long
sColumn = Range("detail").Column

Then you can use:

Cells(1, sColumn)

 

[Upex]

Thanks Joe4,

So use the cells() within the range setting rather then setting the column 'value'...

dim sColumn as long
sColumn = Range("detail").column

Range(Cells(3, sColumn), Cells(Sheet1.Rows.Count, sColumn).End(xlUp))

seems to work as needed - many thanks everyone, much appreciated.