VBA Resize named range

[birchs92]

I am attempting to re-size named range, struggling to find the correct syntax, todate I have the below...any suggestions !!!

Sub ResizeRange()
Dim Nm As Name
For Each Nm In ThisWorkbook.Names
If Nm.Name Like "*d*" And Not Nm.Name Like "*!*" Then
Nm.Resize(Nm.Rows.Count + 1, Nm.Columns.Count).Resize = "Nm"
End If
Next Nm
End Sub

Many Thanks
Simon

 

[VoG]

Perhaps

Range(Nm.Name).Resize(Range(Nm.Name).Rows.Count + 1, Range(Nm.Name).Columns.Count).Resize.Name = "Nm"

 

[CharlesChuckieCharles]

or

ActiveWorkbook.Names(nm.name).RefersToRange.resize(Names(nm.name).RefersToRange.Rows.count+1,Names(nm.name).RefersToRange.Columns.Count)

 

[birchs92]

VoG Thanks

CharlesChuckieCharles Thanks


VoG

I have used:

Range(Nm.Name).Resize(5000, Range(Nm.Name).Columns.Count).Resize.Name = Nm.Name

When I used 'Range(Nm.Name).Rows.Count' just when down to 65535 (max possible row), how could I have used last non-blank cell ?

Many Thanks
Simon

 

[VoG]

Perhaps like this

LR = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
Range(Nm.Name).Resize(LR, Range(Nm.Name).Columns.Count).Resize.Name = Nm.Name

 

[birchs92]

Thank your your help

 

[CharlesChuckieCharles]

Activeworkbook.Names(nm.name).RefersToRange.Rows.count
will give you the row count of the named range