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

 

[Marcelo Branco]

Did you consider converting the data ranges into tables?

You can name a table, for example, Table1 and in your code use
SourceData:= "Table1"

I think it's the easiest way to handle new data (rows) because tables automatically adjust.

M.

 

[Roderick_E]

Ooops here the edited modified function:

Option Compare Text 'ignore text case
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(starthead) <> "" 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:=starthead, 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
endcol = lastcol
End If
rng = ws.Cells(startrow, startcol).Address & ":" & ActiveSheet.Cells(lastrow, endcol).Address
poprange = "'" & ws.Name & "'!" & rng
End Function

 

[Roderick_E]

Thanks but true tables often do some pretty weird anticipatory things. I'd prefer to stick to data sheets.

 

[Roderick_E]

I think I'm getting closer. It looks like the result of poprange function which produces a string for INDIRECT to process is not produced initially in the name range field. If that makes sense. It's not properly "evaluating"??? so INDIRECT is not seeing the result of the poprange function perhaps? How to fix?

 

[Roderick_E]

For example, my poprange function produces this exact string: "'test sheet'!$H$5:$K$10" which is the sheet and the range. If I edit the named range to be =indirect("'test sheet'!$H$5:$K$10") it works fine but if I change it =indirect(poprange("test sheet","test1","test4")) it will fail. HOWEVER, if I type into the spread sheet =poprange("test sheet","test1","test4") it results in "'test sheet'!$H$5:$K$10"

 

[SpillerBD]

Way back before I knew PowerQuery or happily used Tables, I would use a range definition such as the following.

rng= ws.Range("H6").CurrentRegion

 

[Roderick_E]

Way back before I knew PowerQuery or happily used Tables, I would use a range definition such as the following.

rng= ws.Range("H6").CurrentRegion

Excellent piece of code. Thanks for sharing

 

[Roderick_E]

Way back before I knew PowerQuery or happily used Tables, I would use a range definition such as the following.

rng= ws.Range("H6").CurrentRegion

Weird when I tried using this within a function it merely returns the initial range, such as H6.
My test code:

MsgBox ws.Range(ws.Cells(startrow, startcol).Address).CurrentRegion.Address

ws.Range(ws.Cells(startrow, startcol).Address is $C$18 and yet msgbox is returning $C$18 even though the boundary is $G$23

 

[SpillerBD]

rng dimension is wrong.
Dim rng as Range