Variable Names Range - Help Needed

[Les]

Hi all,

I am using the code below to try and resize the range in case a record has
been added to the bottom of this range. I am not sure if the "Old" range must
first be deleted before adding it again. I get a "Run-Time error '13': Type
mismatch" message when i run it at the point mentioned below.

Any help much appreciated.


Private Sub UserForm_Initialize()
'
Dim wb As Workbook, sh As Worksheet
Set wb = Workbooks("Logistics_Cost_TM_Input.xls")
Set sh = Sheets("Part_Family")

'Application.Workbooks(wb).Names("Part_Family_Description").Delete<= ??

'<<== ERROR MESSAGE AT THIS POINT ======
Workbooks(wb).Names.Add Name:="Part_Family_Description", _
RefersTo:=Workbooks(wb).Sheets("sh").Range("D3",
Range("D65536").End(xlUp))

cmb_PrtFam.RowSource = "Part_Family!Part_Family_Description"
End Sub

 

[Mike H]

Les,

Maybe this

Dim rng As Range
Set rng = Range("Part_Family_Description")
lastrow = Cells(Cells.Rows.Count, "D").End(xlUp).Row
added = lastrow - rng.Rows.Count
rng.Resize(rng.Rows.Count + added).Name = "Part_Family_Description"

Mike

 

[Mike H]

I should have added this will also reduce the size of the named range if
records are deleted

Mike

 

[Bob Phillips]

Private Sub UserForm_Initialize()
'
Dim wb As Workbook, sh As Worksheet
Set wb = Workbooks("Logistics_Cost_TM_Input.xls")
Set sh = Sheets("Part_Family")

wb.Names.Add Name:="Part_Family_Description", _
RefersTo:="='" & sh.Name & "'!" & Range(sh.Range("D3"),
sh.Range("D65536").End(xlUp)).Address


cmb_PrtFam.RowSource = "Part_Family!Part_Family_Description"
End Sub

 

[Les]

Thanx Mike H
--
Les

Les,

Maybe this

Dim rng As Range
Set rng = Range("Part_Family_Description")
lastrow = Cells(Cells.Rows.Count, "D").End(xlUp).Row
added = lastrow - rng.Rows.Count
rng.Resize(rng.Rows.Count + added).Name = "Part_Family_Description"

Mike