Making a Dynamic named range

[Roderick_E]

I work with pivot tables a lot and needed a way to make the various datasets dynamic in case I want to add or delete rows or columns.
I created a FUNCTION that I got to work once but can't seem to duplicate it.

STEPS:
1. Add function to workbook
2. Create a named range Formulas/Name Manager/New with a "Refers to:" as =indirect(poprange("Sheet1"))
3. Create a pivot table and set its source as the name of the named range. IT NOW FAILS HERE. "Invalid Ref"

See function:

Function poprange(wsname As String, Optional starthead As String)
'can supply optional header key word to start or leave blank and use for populated row and column
'must supply target sheet as name of sheet.  Example =poprange("Sheet1")
Dim firstrow, lastrow, firstcol, lastcol, startrow, startcol, endcol As Long
Dim ws As Worksheet
Dim rng As String
On Error Resume Next
Set ws = ActiveWorkbook.Sheets(wsname)
firstrow = ws.Cells.Find(What:="*", SearchDirection:=xlNext, SearchOrder:=xlByRows).Row
lastrow = ws.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
firstcol = ws.Cells.Find(What:="*", SearchDirection:=xlNext, SearchOrder:=xlByColumns).Column
lastcol = ws.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
If Trim(findhead) <> "" Then
startrow = ws.Cells.Find(What:=starthead, After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
        MatchCase:=False, SearchFormat:=False).Row
startcol = ws.Cells.Find(What:=findhead, After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
        MatchCase:=False, SearchFormat:=False).Column
        End If


        Resume Next
If startrow = 0 Then
startrow = firstrow
startcol = firstcol
End If


rng = ws.Cells(startrow, startcol).Address & ":" & ActiveSheet.Cells(lastrow, lastcol).Address
poprange = "'" & ws.Name & "'!" & rng
End Function

Again, I got it to work fine but can't duplicate it again.

Also, I want to add an "endhead" optional arg so user can indicate where the range columns should end. How does Excel/VBA know if the optional arg the user provides it arg1 or arg2?

See modified:

Function poprange(wsname As String, Optional starthead As String, Optional endhead As String)
'can supply optional header key word to start or leave blank and use for populated row and column
'must supply target sheet as name of sheet.  Example =poprange("Sheet1")
Dim firstrow, lastrow, firstcol, lastcol, startrow, startcol, endcol As Long
Dim ws As Worksheet
Dim rng As String
On Error Resume Next
Set ws = ActiveWorkbook.Sheets(wsname)
firstrow = ws.Cells.Find(What:="*", SearchDirection:=xlNext, SearchOrder:=xlByRows).Row
lastrow = ws.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
firstcol = ws.Cells.Find(What:="*", SearchDirection:=xlNext, SearchOrder:=xlByColumns).Column
lastcol = ws.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
If Trim(findhead) <> "" Then
startrow = ws.Cells.Find(What:=starthead, After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
        MatchCase:=False, SearchFormat:=False).Row
startcol = ws.Cells.Find(What:=findhead, After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
        MatchCase:=False, SearchFormat:=False).Column
endcol = ws.Cells.Find(What:=endhead, After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
        MatchCase:=False, SearchFormat:=False).Column
        End If
        Resume Next
If startrow = 0 Then
startrow = firstrow
startcol = firstcol
End If
If endcol = 0 Then
lastcol = endcol
End If
rng = ws.Cells(startrow, startcol).Address & ":" & ActiveSheet.Cells(lastrow, lastcol).Address
poprange = "'" & ws.Name & "'!" & rng
End Function

 

[Roderick_E]

ugh, it's still not working

Dim crgn As Range
If Trim(endhead) = "" Then
crgn = ws.Range(ws.Cells(startrow, startcol).Address).CurrentRegion.Address
MsgBox crgn
End If

Still returning $C$18 which is what ws.Cells(startrow, startcol).Address is. Expecting $G$23