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.
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.