Defining Ranges and the benefits of

[Abgar]

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:

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

 

[xld]

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:

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

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

 

[Abgar]

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?

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