Previous Topic Next Topic

Macro to delete rows between named ranges in excel

Closed
prafulk Posts 1 Registration date Monday July 15, 2013 Status Member Last seen July 15, 2013 - Jul 15, 2013 at 05:46 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Jul 16, 2013 at 12:02 PM
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
Related:

1 response

Answer 1 / 1
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Jul 16, 2013 at 12:02 PM
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