Macro to Name a dynamic Row/Col range

[Cheryl4g]

There is a lot of info on dynamic ranges but I can't figure out how to put it together for what I need and hopefully someone can help me.

I have a spreadsheet with the headers on row 1 col 1 to col (whatever) and the data right below it. I need the macro to select all the data from row 1 down to the last row of data and all the data from the first column to the last column with a header and name the range "Data".

First column of data is usually text but it would be useful to know how to use it for number or text number combinations as well.

Thanks for the help,

 

[SydneyGeek]

If the table contains no blank rows or columns, try:

Range("A1").CurrentRegion.Select
ActiveWorkbook.Names.Add Name:="Data", RefersTo:=Selection

Denis

 

[Cheryl4g]

Awesome- thank you.

 

[Cheryl4g]

What if the headers start on row 34 instead of row 1? Is there a way to alter the code to work for that or does that make it too complex?

 

[SydneyGeek]

Change the first line. Change Range("A1") to Range("A34").

If the headers could be in any row except 1, and there is *nothing else* in Column A above the headers, you can use

Range("A1").End(xlDown).CurrentRegion.Select
ActiveWorkbook.Names.Add Name:="Data", RefersTo:=Selection

Denis

 

[bjflink]

Hi, I have a slight modification to this that I am having trouble with and would greatly appreciate your help.

I am trying to rename a range after an autofilter is applied through a macro. The column secetion is fixed, A:J, and the rows are variable. This is the code I have been trying, which gives me a compile error.

.Range(.Range("A3:J3"), _
.Range("A3").End(xlDown)).Name = "filtercriteria"

 

[SydneyGeek]

Something like...

Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Select
ActiveWorkbook.Names.Add Name:="FilterData", RefersTo:=Selection

But if you recreate the name for another filter, you should delete the existing name first and then create the replacement name.

Denis