Help deleting rows and creating range name via VBA

[EssKayKay]

I hope I can explain this properly. I have a couple questions that are somewhat related if you please.

I’m trying to create a VBA routine that deletes all consecutive rows that contain data starting in B33. The number of rows will vary from Row 33 on down to Row XXXX.

I was attempting to create a range called Data by doing a Macro Recording. In this case, it hardcodes in the last row (392). However, the next time I need to run this macro the last row may not be 392.

Range("B33").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Names.Add Name:="Data", RefersToR1C1:= _
   "=Amortize!R33C2:R392C2"
ActiveWorkbook.Names("Data").Comment = ""

Thanks,
Steve K.

 

[dermie_72]

if you add:

LR = Cells(Rows.count, "B").End(xlUp).Row

above the top line, then replace that following code:

ActiveWorkbook.Names.Add Name:="Data", RefersToR1C1:= _
   "=Amortize!R33C2:R392C2"

with:

ActiveWorkbook.Names.Add Name:="Data", RefersToR1C1:= _
   "=Amortize!R33C2:R" & LR & "C2"

that will take it to the last active cell in Row B each time without having to chnage the code.

 

[EssKayKay]

if you add:

LR = Cells(Rows.count, "B").End(xlUp).Row

above the top line, then replace that following code:

ActiveWorkbook.Names.Add Name:="Data", RefersToR1C1:= _
   "=Amortize!R33C2:R392C2"

with:

ActiveWorkbook.Names.Add Name:="Data", RefersToR1C1:= _
   "=Amortize!R33C2:R" & LR & "C2"

that will take it to the last active cell in Row B each time without having to chnage the code.

Thank you Dermie. This has me going in the right direction. I have more things to look at. I'm sure as this progresses I will be back but now I'm on the right path.

Much appreciated,
Steve