Can I refer to a column by its Named Range?

mcomp72

Active Member
Joined
Aug 14, 2016
Messages
308
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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.

Code:
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?
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
What is your ultimate Goal?

You want to get the Sum of all values in two different columns?
 
Upvote 0
Maybe this way...

Summation = Intersect([TotalsColumn], Rows(1)) + Intersect([TotalsColumn], Rows(2))
Intersect([TotalsColumn], Rows(4)) = Summation
 
Upvote 0
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?
 
Upvote 0
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.

Code:
Sub Using_Column_Name()
'Modified  7/25/2018  11:30:09 PM  EDT
Range("A5:E12").ClearContents
[A5:E12].ClearContents
End Sub
 
Upvote 0
You can use
Code:
Intersect([MyColumn], Rows("7:11")).ClearContents
This will clear the contents of rows 7 to 11 in the named column
 
Upvote 0

Forum statistics

Threads
1,224,824
Messages
6,181,187
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top