Resize range into a range specified in a cell

[luzikedy]

Hi,

Here is what I did by combining few things I found on the forums.


Sub Resize_Named_Range()


Dim wb As Workbook
Dim nr As Name
Dim rng As range
Set rng = Sheets("data").range("a1") "This cell has cell reference in it - sheet1!$A$1:$A$10"
Set wb = ActiveWorkbook
Set nr = wb.Names.Item("Produkcja_AD")
nr.RefersTo = rng.Text
End Sub

I almost works, the only but is that instead resizing the range into something like this:

=sheet1!$A$1:$A$10


I get "" outside

="sheet1!$A$1:$A$10"


Thx for help with getting rid of "" in the result.

 

[Sektor]

Could you be more specific about what you're trying to do?

 

[luzikedy]

I am trying to change one of my named ranges called "Produkcja_AD"

in Sheets("data").range("a1") - I have a result of a formula that calculates the range I want to obtain and it results is:

sheet1!$A$1:$A$10

which I want to be the new range of namedrange "Produkcja_AD"

I hope this is more clear.

 

[Sektor]

Sub F()
    Range(Sheets("data").Range("A1").Text).Name = "Produkcja_AD"
    '// Just test
    MsgBox WorksheetFunction.Count(Range("Produkcja_AD"))
End Sub

 

[luzikedy]

Thx, it does what I needed!

 

[Sektor]

You're welcome!