How to avoid adding a new row to the first row of subtotaled ranges to protect Subtotal formula

Question

I have a Excel sheet, which includes many groups and subgroups. Each subgroup has their own subtotal. And as long as I can't know how many lines users will require for the subgroups, I only left 3 rows blank for each subgroup and created a button which triggers Automatically Copy and Insert Copied Row macro.

Now I need to avoid users to trigger the button while they are on the First and the Last row of any subgroup. (If you copy and insert first row, subtotal formula doesn't recognise the new added line because it will be out of the range and Last row has another specific formulas which I don't want users to copy above)

I've been searching SO and the other Excel blogs for a long time but because of my list is very dynamic and have many different subtotals none of them was suitable to my case.

How can I define all of the first and last rows of my different subtotal ranges (and maybe their Title's rows) to avoid them being copied and then inserted above?

Answer 1

I can imagine 2 ways to do this. When the "New Row" button is pressed check if the current row is a headline/subheadline whatever …

1. by checking its format (eg specific background color etc).

   If Not Worksheets("Sheet1").Range("A" & iRow).Interior.Color = 15004911 Then 
       'copy that row
   End If
   

2. or by using an extra helper column that specifies the rows as header rows or non copyable rows. If you don't want the helper column to be seen you can hide it.

   If Not Worksheets("Sheet1").Range("X" & iRow).Value = "Header" Then 
       'copy that row
   End If
   'where X is the helper column
   

   And if it is a headline row then deny the copy process.