Defining Ranges and the benefits of

Abgar

Active Member
Joined
Jun 20, 2009
Messages
255
Hi Guys,

I've heard a bit about defining ranges lately and i wanted to get a feel for how they are actually useful.
When i say defining ranges, I am talking specifically about the following:
I have macro which is run on many different spreadsheets - all in the same format. Currently, I have several formulas like:
"=SUMPRODUCT(--($C1:$C65535=""PKT""),--($N1:$N65535=""Internet""),($R1:$R65535))"

Now i've heard that what this formula is actually doing is searching EVERY single cell in Column C for instances of PKT etc, and that if i defined column C as a range (lets say "Call Type") then it would automatically only search all of the USED cells in that range. Is that right? And if so, is that likely to speed the macro up?

I had a quick look at defining names and found this:
Code:
Columns("D:D").Select
    ActiveWorkbook.Names.Add Name:="ColumnD", RefersToR1C1:="=Sheet1!C4"
but the problem is that the "=Sheet1!C4" section is variable as the active sheet name will always be different. Is there a way to make it read the active sheet name, instead of just Sheet1?

And my last question (i promise) is - Is there a way to automatically define each used column as a separate range, and call that range name the value of row1 in that range? (i.e - Cell A1 is called Date, so can i automatically define Column A as a range that is called Date? - Plus, some of the cells in row 1 will have a space in it (Call Type) - can i replace the space with an "_" (underscore)?

Thanks so much for your help guys.

Appreciate any feedback given :)
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

xld

Banned
Joined
Feb 8, 2003
Messages
5,378
Now i've heard that what this formula is actually doing is searching EVERY single cell in Column C for instances of PKT etc, and that if i defined column C as a range (lets say "Call Type") then it would automatically only search all of the USED cells in that range. Is that right?

Nope. A range is the same, defined or not.

And if so, is that likely to speed the macro up?

As it is not so, ....

I had a quick look at defining names and found this:
Code:
Columns("D:D").Select
    ActiveWorkbook.Names.Add Name:="ColumnD", RefersToR1C1:="=Sheet1!C4"
but the problem is that the "=Sheet1!C4" section is variable as the active sheet name will always be different. Is there a way to make it read the active sheet name, instead of just Sheet1?

Activesheet.Range("C4").Name = "ColumnD"

And my last question (i promise) is - Is there a way to automatically define each used column as a separate range, and call that range name the value of row1 in that range? (i.e - Cell A1 is called Date, so can i automatically define Column A as a range that is called Date? - Plus, some of the cells in row 1 will have a space in it (Call Type) - can i replace the space with an "_" (underscore)?

You could do on workbook open, but it would be wasteful, you should only name ranges that you need, and you shouldn't define whole columns (although there are exceptions), it is better to define a variable range

Code:
ActiveWorkbook.Names.Add Name:="ColumnDData", RefersToR1C1:="=Offset(Sheet1!$D$1,,,COUNTA(Sheet1!$D:$D),1)"
 

Abgar

Active Member
Joined
Jun 20, 2009
Messages
255
Thanks for that XLD.

So defining the variable range would speed up the macro?

And the code you have provided below - does that define a range for a colum as D1 to the last used cell in Column D?

Code:
ActiveWorkbook.Names.Add Name:="ColumnDData", RefersToR1C1:="=Offset(Sheet1!$D$1,,,COUNTA(Sheet1!$D:$D),1)"

Also, with the Sheet1!$D$1 bit, would i just change that to ActiveSheet.Range$D$1 to make it work for the variable sheet name?

Thanks again
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,316
Members
414,053
Latest member
Dual Showman

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
Top