Excel VBA choose CurrentRegion and create Named Range

Rx_

Nothing In Moderation
Local time
Today, 17:46
Joined
Oct 22, 2009
Messages
2,803
Spent a while trying to find the best way to select the Current Region (the rectangular area of data) and create a Named Range in Excel 2010.

Saw lots of multi-line solutions. Came up with this and it works.

Note that my data with header always starts in Cell A5. The number of records below the header can vary from 1 to 65,000 or more (Excel 2010).
This code highlights the Cell A5 - determines the CurrentRegion - then creates a NamedRange "Data12"
The Data12 of course can be replaced with a string variable as could the A5. My Excel reports return a recordset from MS Access databases. While the recordset object recordcount is known, and the beginning point (in this case A5), this code below is just too simple not to use. LOL

ActiveWorkbook.Names.Add Name:="Data12", RefersToR1C1Local:=Range("A5").CurrentRegion

My next step is to use the Data12 in a CrossTab Excel report.

Note: This was tested in Excel 2010.
 

Users who are viewing this thread

Back
Top Bottom