Need help converting offset formula for dynamic range in vba

[michaelsmith559]

Here is the current macro:

Sub Insert_Dynamic_Range()
Dim myText As String, myRange As Range, lastRow As Long

myText = Application.InputBox("Enter a Name for Range", Type:=2)
Set myRange = Application.InputBox("Select Where To Put Name", Type:=8)
myRange = myText

With ActiveSheet
lastRow = Cells(Rows.Count, myRange.Column).End(xlUp).Row
    ActiveSheet.Names.Add Name:=myText, _
    RefersTo:="OFFSET(myRange.Offset(1,0).Address,0,0,MATCH(1E+306,myRange.Column:myRange.Column),1)"
End With

End Sub

Here is sheet 1:

Excel 2012
	A	B
1
2	5
3	6
4	7
5	3
6	2
7	1
8	8
9	9
Sheet1

Here is sheet 1 after I run macro:

Excel 2012
	A	B
1	Prices	#VALUE!
2	5
3	6
4	7
5	3
6	2
7	1
8	8
9	9
Sheet1

Cell Formulas
Range	Formula
B1	=SUM(Prices)

Named Ranges
Name	Refers To	Cells
Sheet1!Prices	="OFFSET(myRange.Offset(1,0).Address,0,0,MATCH(1E+306,myRange.Column:myRange.Column),1)"

I am trying to replace this formula:
Offset($A$2,0,0,Match(1E+306,$A:$A),1)

with something like this:
OFFSET(myRange.Offset(1,0).Address,0,0,MATCH(1E+306,myRange.Column:myRange.Column),1)

 

[michaelsmith559]

I see, I forgot the = signs. The code works perfect now. It allows a user to create a dynamic named range for numbers with blank cells just by entering a name and selecting where to put the name. I just have a few more things I may add to it. Appreciate the help.

 

[Jeffrey Mahoney]

I'm going to post this because I worked on it for a half hour. Take what you need. With this code, you select the range including the header. It makes the range out of the values below.

Sub Insert_Dynamic_Range2()
  Dim myText As String, myRange As Range, i As String, j As String
  Dim Cel As Range
  Dim A As String
  Dim P As String
  
  myText = Application.InputBox("Enter a Name for Range", Type:=2)
  Set myRange = Application.InputBox("Select Where To Put Name", Type:=8)
  If myRange Is Nothing Then Exit Sub
  For Each Cel In myRange
    i = Cel.Address(0, 0)
    A = "'" & Cel.Parent.Name & "'!" & Cel.Address
    Exit For
  Next Cel
  j = Left(i, 1)
  i = A
  
  A = "=Offset(" & i & ", 1, 0, Match(1E+306, " & "$" & j & ":" & "$" & j & ")-1, 1)"
  ActiveSheet.Names.Add Name:=myText, RefersTo:=A
  
End Sub

 

[michaelsmith559]

Here is what the macro does (although I am not finished with it yet). Suppose I want to create a dynamic named range. I want to call the named range "Prices". For example, setup sheet 1 like I have here:

Excel 2012
	A	B	C
1
2	5	#NAME?
3	6
4	4
5	7
6	3
7	7
8	2
9	8
10	1
11	9
12
Sheet1

Cell Formulas
Range	Formula
B2	=SUM(Prices)

Sheet after macro runs:

Excel 2012
	A	B	C
1	Prices
2	5	52
3	6
4	4
5	7
6	3
7	7
8	2
9	8
10	1
11	9
12
Sheet1

Cell Formulas
Range	Formula
B2	=SUM(Prices)

Named Ranges
Name	Refers To	Cells
Sheet1!Prices	=OFFSET(Sheet1!$A$2, 0, 0, MATCH(1E+306,Sheet1!$A:$A), 1)

Check the Formulas; Name Manager

You should see the named range and the appropriate formula. All I had to enter was the name I wanted to call the range and select the cell I wanted the name placed in.

Sub Insert_Dynamic_Range_Numbers()
Dim myText As String, myRange As Range, i As Variant, j As Variant

With ActiveSheet

myText = Application.InputBox("Enter a Name for Dynamic Range and Column Heading", Type:=2)
Set myRange = Application.InputBox("Select First Row for Column of Dynamic Range", Type:=8)
myRange = myText

i = myRange.Offset(1, 0).Address
j = Split(i, "$")(1)

ActiveSheet.Names.Add Name:=myText, _
    RefersTo:="=Offset(" & i & ", 0, 0, Match(1E+306, " & "$" & j & ":" & "$" & j & "), 1)"

End With

End Sub

 

[michaelsmith559]

Sorry, the above code line: myText = Application.InputBox("Enter a Name for Dynamic Range and Column Heading", Type:=2) can be change to this:

myText = InputBox("Enter a Name for Dynamic Range and Column Heading")

 

[michaelsmith559]

Here is my finished macro. It easily creates a dynamic named range for a range of numbers, including blanks. If your starting cell is blank it puts the name in that cell and if the starting cell contains a number it moves the numbers down one row and inputs the name in the first cell. The refers to formula is automatically filled in. Here is the macro:

Sub Insert_Dynamic_Range_Numbers()
Dim myText As String, myRange As Range, i As Variant, j As Variant
Dim k As Range, l As Variant

With ActiveSheet

myText = InputBox("Enter a Name for Dynamic Range and Column Heading")
Set k = Application.InputBox("Select First Row for Column of Dynamic Range", Type:=8)
l = k.Address

If Application.WorksheetFunction.IsNumber(Range(l)) <> True Then 'Uses this if name cell is blank
    Set myRange = Range(l)
        myRange = myText

    i = myRange.Offset(1, 0).Address
    j = Split(i, "$")(1)

ActiveSheet.Names.Add Name:=myText, _
    RefersTo:="=Offset(" & i & ", 0, 0, Match(1E+306, " & "$" & j & ":" & "$" & j & "), 1)"

ElseIf Application.WorksheetFunction.IsText(Range(l)) <> True Then 'Uses this if name cell is number
Range(Cells(k.Row, k.Column), Cells(Rows.Count, k.Column).End(xlUp)).Cut Destination:=k.Offset(1, 0)
    Set myRange = Range(l)
        myRange = myText

    i = myRange.Offset(1, 0).Address
    j = Split(i, "$")(1)

ActiveSheet.Names.Add Name:=myText, _
    RefersTo:="=Offset(" & i & ", 0, 0, Match(1E+306, " & "$" & j & ":" & "$" & j & "), 1)"


End If

End With

End Sub