macro to delete rows between named ranges in excel Closed

Question

How do I delete all rows between named ranges?


I have a scenario below
Name Range 1 : SUBHEADER_001 (A1:E1). Displays Headers
Name Range 2 : SUBFOOTER_001 (A40:E40). Displays legends and dates.


Between these ranges I am inserting data from sql server. The cell ranges of the SUBFOOTER_001 will change depending on the database records inserted.


I would like to know how to delete these database values between named ranges SUBHEADER_001 and SUBFOOTER_001 . Thank you.


I have googled regarding this but came across the below code snippet which will delete all rows below the range. Rows(Range("MYRANGE").Cells(Range("MYRANGE").Rows.Count, 1).Row + 1 & ":65536").Delete

TrowaD · Answer

Hi Prafulk,

This code will delete rows between your named ranges SUBHEADER_001 and SUBFOOTER_001:
Sub Test()
Dim fRow, lRow As Integer
fRow = Range("SUBHEADER_001").Row + 1
lRow = Range("SUBFOOTER_001").Row - 1
If lRow < 2 Then Exit Sub
Range(Rows(fRow), Rows(lRow)).Delete
End Sub
Best regards,
Trowa

Macro to delete rows between named ranges in excel

1 response

Similar discussions

Subscribe To Our Newsletter!