Can I refer to a column by its Named Range?

[mcomp72]

On my worksheets, I have named some columns using the Name Manager. In VBA, I was hoping to be able to refer to the columns by the names I gave them, but I can't seem to figure out how to do it.

Here's a test sub I wrote to try to figure it out.

Sub Test()

Dim Summation As Integer

Summation = Range("TotalsColumn" & "1").Value + Range("TotalsColumn" & "2").Value

Range("TotalsColumn" & "4") = Summation

End Sub

(TotalsColumn is the Named Range for the entire column J.)

Unfortunately, this doesn't work. It returns Run-time error 1004: Application-defined or object-defined error.

Anyone know of a way to do what I am trying to do?

 

[My Aswer Is This]

What is your ultimate Goal?

You want to get the Sum of all values in two different columns?

 

[Rick Rothstein]

Maybe this way...

Summation = Intersect([TotalsColumn], Rows(1)) + Intersect([TotalsColumn], Rows(2))
Intersect([TotalsColumn], Rows(4)) = Summation

 

[mcomp72]

My ultimate goal is to do a lot of different stuff in VBA, but just refer to the columns as the name I have given them. That way, if I insert a column into my sheet in the future, I don't have to go into my code and change the columns all over the place. So I would like to be able to refer to the columns by their names, but do everything I would normally do with the code. So, for instance, if I wanted to clear some cells, like such: Range("A5:E12").ClearContents, I would like to be able to do that with the names I gave the columns, rather than using 'A' and 'E'.

Rick, the code you posted works for the example I posted. Thanks for that. Unfortunately, I've tried to figure out how to use the Intersect command to clear contents from certain cells, but I couldn't figure it out. Intersect is a new one for me. Is it possible to use Intersect as a way to refer to a column by it's name for anything I'd want to do in VBA, or is it not designed for that. If not, any other way?

 

[My Aswer Is This]

I understand what you want but do not have a answer how to do that.
In your one example you can shorten you code using this:

In case you wanted to know.
But that does not answer your question.

Sub Using_Column_Name()
'Modified  7/25/2018  11:30:09 PM  EDT
Range("A5:E12").ClearContents
[A5:E12].ClearContents
End Sub

 

[Fluff]

You can use

Intersect([MyColumn], Rows("7:11")).ClearContents

This will clear the contents of rows 7 to 11 in the named column