Forum Discussion

PeterYac's avatar
PeterYac
Copper Contributor
Apr 24, 2020

Assign a name to a range

I need help assigning a name to a range and would welcome some help.

The data will vary in terms of number of rows and number of columns.

 

Below is some of my code along with questions - any help would be greatly appreciated.

 

many thanks

Peter

______________________________________________________________________________

 

 

 

' Finds Final Row of data - This bit works OK
FinalDataRow = Sheets(SheetName).Cells(Rows.Count, 1).End(xlUp).Row

 

'Find Final Column of Data - this does not work, How can I find the last column of data

'FinalDataCol = Sheets(SheetName).Cells(Columns.Count, 1).End(xlRight).Column


' Set DV_Data name to new range

' I nee some way of converting the last column number (once i get it) in to a letter so that

' i can use the RANGE command.
Range("A1:???" & FinalDataRow).Name = "DV_Data"

Macros and VBA

2 Replies

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Apr 27, 2020

    PeterYac Not sure if this will work in your specific situation, but you might want to try a bit of code similar to below:

    Selection.SpecialCells(xlCellTypeLastCell).Select
    
lRow = Selection.Row
lCol = Selection.Column

ActiveWorkbook.Names.Add Name:="NamedRng", RefersToR1C1:= "=Sheet1!R1C1:R" & lRow & "C" & lCol

    It will find the last cell in the worksheet and that define a named range from A1 (top left) to that cell (bottom right).