adding new rows in excel without breaking a vba macro that uses Range.Value

[wberg82]

How do I auto update a macro so that if I add rows on a sheet it updates the range the macro is pulling from? For example, in the following code, my info is pulling from row 9. If I add to rows somewhere above this, this information is on now on row 11. However, I now need the macro to pull from row 11. I don't want to have to do into VB and manually update every code as this would take me forever......

With Sheets("Hardware Load List").Range("C" & Rows.Count).End(xlUp).Offset(1)
.Offset(, 1).Resize(, 5).Value = Range("D9:H9").Value
.Offset(, 6).Value = Range("J11").Value
.Value = Range("B9").Value
End With

 

[offthelip]

Used a named range, and refer to the named range in VBA, the named range will move if you insert rows above.
To refer to a named range called test you address it:

Range("test")