Go to the End of a database in Excel

[dyana]

How do you write a VBA code that highlights an entire data range within Excel and names it "datarange"? I need this code to basically start at column "G" through "AF" and move to the bottom of the database. The database size could vary, meaning it will occupy different numbers of rows.

Thanks
dp

 

[euskadi]

If you're trying to name the range by creating a named range on a spreadsheet, you don't have to use VBA.

Create a name range name "datarange" (or whatever) and enter this formula, "datarange" will reflect the proper size of your data and it will update automatically.

=OFFSET(Sheet1!$G$1:$AF$1,0,0,COUNTA(Sheet1!$G:$G)-1)

 

[dyana]

I tried this and it did not work.

Help Please!

 

[LSTAN]

Hi dyana

You can try this code

Sub nameRange()

Dim myCol As Integer, myRng As Range
Dim myRow As Integer, myRng2 As Range
Dim initialRow As Integer, initialCol As Integer
Dim referRng As String

Set myRng = Sheets("sheet1&quot.Range("G1:AF1&quot
myCol = Application.CountA(myRng) + 6 'the offset is 6 from the first column

Set myRng2 = Sheets("sheet1&quot.Range("G:G&quot
myRow = Application.CountA(myRng2)

Sheets("sheet1&quot.Range("G1&quot.Select
initialRow = ActiveCell.Row
initialCol = ActiveCell.Column

ActiveSheet.Range(Cells(initialRow, initialCol), Cells(myRow, myCol)).Select
referRng = "=Sheet1!R" & initialRow & "C" & initialCol & ":R" & myRow & "C" & myCol
ActiveWorkbook.Names.Add Name:="dataRange", RefersToR1C1:=referRng

ActiveSheet.Cells(myRow, myCol).Select

End Sub

Hope this helps.

rgrds
LSTAN

 

[dyana]

LSTAN,

You have really helped me out this week. My first week at Tek-tips has been great because of your support.

I appreciate your assistance.

Dyana

 

[LSTAN]

You are welcome. That's what this forum for.

rgrds
LSTAN