Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why is inserting rows throwing off my hidden rows
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why is inserting rows throwing off my hidden rows
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why is inserting rows throwing off my hidden rows
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).I nsert Shift:=xlDown Application.CutCopyMode = False |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why is inserting rows throwing off my hidden rows
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.) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why is inserting rows throwing off my hidden rows
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro code to hide rows and not calculate hidden rows | Excel Discussion (Misc queries) | |||
Count number of rows, where non relevant rows are hidden | Excel Discussion (Misc queries) | |||
Excel 2003 -Rows hidden. Scrolling unhides rows ! How do I stop th | Excel Discussion (Misc queries) | |||
Inserting multiple rows in excel with data in consecutive rows | Excel Programming | |||
deleting hidden rows so i can print only the rows showing?????? | Excel Worksheet Functions |