Deleting Rows - Defined Names

[DrPepper]

How can I delete a row without effecting a Defined Name?

I have a range (B2:B47) setup as a Defined Name, which I use as a Data Validation List. When I delete a row, say row 7, it changes my Defined Name range to (B2:B46). So now if I fill my B column up to 47... the last name will not show up in my drop down list.

Very frustrating... any help would be appreciated.

[joms]

Hi DrPepper, if you specify to delete the whole row, then the whole row will be deleted
why not specify which cells to be deleted..check this code below..
it will delete 10 cells which is offset from b7..hope this is what you mean..
i'm not an expert..so guys around here might have a better idea.. :)


Dim x As Integer
For x = 1 To 10
Range("B7").Offset(0, x).Select
Selection.ClearContents
Next x

[slamet Harto]

After delete row and run this code, or you can use ws change even.

Sub redefined()
Dim Lastrow as long, Rng as range, NewRng as range
With ActiveSheet
lastrow = Cells.SpecialCells(xlCellTypeLastCell).Row
End With
Set Rng = ActiveSheet.Range([B2], "B" & lastrow)
ActiveWorkbook.Names.Add Name:="NewRng", RefersTo:=Rng
End sub