Naming Ranges Based on Final Row

[dpmaki]

I have one column (A) that will change from month to month, but will always define the maximum number of rows that my data set can be held in. I've therefore coded it as follows.

FinalRow = Range("A70000").End(xlUp).Row

Columns B-F will range in the number of rows that they have, but as mentioned will never go beyond column A in number of rows. What I would like to do is name all of these other colums as ranges too.

Can anyone explain how to do this?

 

[Joe4]

Do you mean something like this?

    Dim i As Long
    Dim myRange As Range
    Dim myRangeName As String
    
'   Loop through columns B - F, naming each one from first row down to last used row in that column
    For i = 2 To 6
        Set myRange = Range(Cells(1, i), Cells(Rows.Count, i).End(xlUp))
        myRangeName = "NamedRange" & i
        ActiveWorkbook.Names.Add Name:=myRangeName, RefersTo:=myRange
    Next i

 

[dpmaki]

Well - kind of - the problem that I have though is that not all the columns have something in every cell. That's why I was hoping that I could define the number of rows in the column by using column A, which will always have the maximum number of rows.

 

[Joe4]

So, are you saying that you want all your named ranges for each column to end in the exact same row as column A?
That should be real easy. Just incorporate your code into mine and change one line:

Set myRange = Range(Cells(1, i), Cells([COLOR=red][B]FinalRow[/B][/COLOR], i))

 

[dpmaki]

Do I still need the Dim in the code? Sorry - just began learning VBA and some of the advanced features of excel.

 

[Joe4]

It is generally good practice to Declare you variables in your code. And if you use Option Explicit at the top of your code, it will help identify any undeclared variables (and catch typos!). It is very handy for debugging.

Option Explicit
 
Sub MyMacro()
 
    Dim FinalRow as Long
    Dim i As Long
    Dim myRange As Range
    Dim myRangeName As String
 
'   Get final row from column A
    FinalRow=Cells(Rows.Count,"A").End(xlUp).Row
 
'   Loop through columns B - F, naming each one from first row down to last used row in that column
    For i = 2 To 6
        Set myRange = Range(Cells(1, i), Cells(FinalRow, i))          
        myRangeName = "NamedRange" & i
        ActiveWorkbook.Names.Add Name:=myRangeName, RefersTo:=myRange
    Next i
 
End Sub

Edit: I corrected a typo in my previous post.

 

[dpmaki]

Thanks so much for the help!

 

[Joe4]

Your welcome!