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:
where the LastRow should reference this code which helps account for all the blank cells that are in the column as not everyone responds:
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:
Anyone have any ideas?
Ken
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:
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:
Code:
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:
Code:
Range(Cells(6, 1), Cells(6, LastRow)).Name = "Responded_On"
Anyone have any ideas?
Ken