Why is inserting rows throwing off my hidden rows

[jgeniti]

I have a macro that is using the following code to copy a template row
that is hidden and then it inserts that row below it. The problem is
that all of the hidden rows that are below where I'm inserting are not
changing to account for the added row.

For example.
rows 15, 20 and 25 have templates in them and are hidden
then I will do the following
copy template from row 10
insert template at row 12

Even though all of the data has shifted down 1 row, rows 15, 20 and 25
are still hidden and all of the data that was in those hidden rows are
now showing in rows 16, 21 and 26.

Sheets("Inline").Range("Template").Copy
Sheets("Inline").Range("ResDetEnd").Insert Shift:=xlDown
Application.CutCopyMode = False

Am I forgetting something? Any help would be appreciated.
Thanks,
James

 

[Mark Lincoln]

If you insert a number of cells and shift down, you are only moving the
cells in those columns. This shifts cells that were in the hidden rows
down one row while leaving the other hidden cells unchanged.

If you insert an entire row, the hidden rows will move and their
contents will remain hidden. You could do something like

Worksheets("Sheet1").Rows(12).Insert

to insert a row before row 12, then copy and paste your hidden
information into the new row 12.

 

[jgeniti]

Mark,
That makes perfect since. I changed my code to the following and I'm
still having the same issue. Could this have to do with the fact that
I'm copy the data and doing and insert instead of inserting the rows
and then copying and pasting the data?

Sheets("Inline").Range("IL_Resin_Temp").Copy
Sheets("Inline").Rows(Range("IL_ResDetEnd").Row).Insert Shift:=xlDown
Application.CutCopyMode = False

 

[Mark Lincoln]

Could this have to do with the fact that

I'm copy the data and doing and insert
instead of inserting the rows and then
copying and pasting the data?

Yes. Inserting an *entire row* will automatically shift all lower rows
down while maintaining the hidden attribute of hidden rows. What
you're doing is inserting *cells* which displace lower cells down to
the next row when you use Shift:=xlDown. If a cell is displaced from a
hidden row to one that is not hidden, then any data contained in the
cell will become visible.

You need to insert a row rather than cells if at all possible (thereby
maintaining the hidden status of the hidden rows below) then copy the
needed cells and paste them into the newly-created row.

If you can determine which row number needs to have the insert
performed, put that in an integer variable (I'll call it TheRow) and
use this to insert your new row:

Worksheets("Inline").Rows(TheRow).Insert

Use the same variable to tell Excel where to paste your data. (I'd be
more specific, but I have no access to Excel right now and can't test
anything. I'll try adding more later if you still need it.)

 

[Mark Lincoln]

It just occurred to me that if you are actually copying and pasting
less than an entire row you can shift cells to the right instead of
down:

Shift:=xlShiftToRight

This will leave other rows unaffected, and your code should work with
just the one modification.