-2
var1 = range("named_range1").row + 3
var2 = range("named_range2").row - 2
For i = var1 to var2
    Rows(i).select
    Selection.Delete
Next i

The named ranges have to be the reference because there are insertion of rows between them and the named ranges are the titles which cannot be deleted or changed, basically they are very important.

This part of the code is basically a button to delete all the rows which have been added between those titles during the user's usage, so that when it uses the next time the excel is in it's "default mode".

The code right now is deleting every 2 rows and the row with the 2nd named range.

TylerH
  • 20,799
  • 66
  • 75
  • 101
nussbaumer05
  • 31
  • 5
  • Loop from the bottom up: `For i = var2 to var1 Step -1` – Scott Craner Dec 29 '22 at 19:36
  • When you delete a row/column in excel they all shift, so when you delete row 2, what was row 3 becomes row 2, which you already passed in the loop. [unionize](https://stackoverflow.com/questions/39620661/entirerow-delete-skips-entries-in-for-loop) to avoid that. – Warcupine Dec 29 '22 at 19:37
  • 2
    Or just delete all at one time: `Rows(var1 & ":" & var2).delete` – Scott Craner Dec 29 '22 at 19:37

1 Answers1

0

The basic solution is to make cycle reversive:

var1 = range("named_range1").row + 3
var2 = range("named_range2").row - 2
For i = var2 to var1 Step -1
    Rows(i).Delete
Next i

More elegant solution is to make it using following code:

Range(Range(named_range1).Offset(3), Range(named_range2).Offset(-2)).EntireRow.Delete

Where 3 and -2 is needed shifts

SBlake
  • 1
  • 1