Macro to delete rows between named ranges in excel

Closed
Report
Posts
1
Registration date
Monday July 15, 2013
Status
Member
Last seen
July 15, 2013
-
Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
-
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

1 reply

Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
491
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
0