Named Range Variable

[Tropez]

Greetings all, I need some assistance on a project:

I have a spreadsheet of survey results that has all of the surveys sent out and in the same spreadsheet are their answers to the questions.

| Responded_On | Sent_On | Answer 1 | Answer 2 | Answer 3 |

I'm trying to create a named range thru the following code:

    ActiveWorkbook.Names.Add Name:="Responded_On", RefersToR1C1:= "=Surveys!R6C1:R" & LastRow & "C1"

where the LastRow should reference this code which helps account for all the blank cells that are in the column as not everyone responds:

Sub FindLastRow()
Dim LastRow As Long
    If WorksheetFunction.CountA(Cells) > 0 Then
        'Search for any entry, by searching backwards by Rows.
        LastRow = Cells.Find(What:="*", After:=[A1], _
              SearchOrder:=xlByRows, _
              SearchDirection:=xlPrevious).Row
              ' MsgBox LastRow
    End If
End Sub

The LastRow reference does give me the row number but when the macro gets to this point it simply gives the first cell of the column the named reference instead of the range that I was hoping for.

I've also tried the following code which does not seem to work at all:

    Range(Cells(6, 1), Cells(6, LastRow)).Name = "Responded_On"

Anyone have any ideas?

Ken

 

[HalfAce]

Hello Tropez,
First let's see if I've captured your intent.
As I understand it you want to define a named range (named "Responded_On") to the range in sheet "Surveys", from cell A6 over to column R and down to the last entry in that column. (Yes?)

If that's correct then perhaps something like this...

Sub NameThatRange()
Dim LastRow As Long

With Sheets("Surveys")
    If WorksheetFunction.CountA(Cells) > 0 Then
        'Search for any entry, by searching backwards by Rows.
        LastRow = Sheets("Surveys").Cells.Find(What:="*", After:=[A1], _
              SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    End If
End With

ActiveWorkbook.Names.Add Name:="Responded_On", RefersToR1C1:="=Surveys!R6C1:R" & LastRow & "C18"
    
End Sub

Hope it helps.

 

[Tropez]

Actually, I was wanting to go from Cell A6 (the first entry in the column) to the last entry in that same column, which is A313 right now.

 

[HalfAce]

Ah... I see.
Then let's limit LastRow to search for used cells only in columnA, and then change the range reference for the named range to column 1 instead of 18.

Sub NameThatRange()
Dim LastRow As Long

With Sheets("Surveys")
    If WorksheetFunction.CountA(Cells) > 0 Then
        'Search for any entry, by searching backwards by Rows.
        LastRow = Sheets("Surveys").Columns(1).Find(What:="*", After:=[A1], _
              SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    End If
End With

ActiveWorkbook.Names.Add Name:="Responded_On", RefersToR1C1:="=Surveys!R6C1:R" & LastRow & "C1"
    
End Sub

I don't know where I got column R from. Must be those R1C1 references. They screw me up every time. :x

 

[Tropez]

That got it, although I do need it to reference the last used row for the entire table area and not just the one column. As I've got some formulas that do a count of the responses based on these named areas, and for some reason when you are doing a sumproduct lookup with named ranges they all have to be of the same length or number of cells (rows in my case).

THANK YOU!!!

 

[HalfAce]

That got it, although I do need it to reference the last used row for the entire table area and not just the one column.

Oh. (I was wondering if that might be the case.)
If you haven't already got it squared away, you can make your range be just in columnA and go as far down as any value in any column by reverting this line:
LastRow = Sheets("Surveys").Columns(1).Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
back to what you had originally:
LastRow = Sheets("Surveys").Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

Sorry about that.