Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Blank Rows when Pivot Table included on sheet
Hi,
I've run code to delete blank rows which works fine. That is, it works fine until I'm deleting blank rows on a sheet which also contains a few pivot tables. I encounter an error message regarding not being able to change pivot table. Here is my code: Thanks! Dim x As Long With ActiveSheet For x = .Cells.SpecialCells(xlCellTypeLastCell).Row _ To 1 Step -1 If WorksheetFunction.CountA(.Rows(x)) = 0 Then ActiveSheet.Rows(x).Delete End If Next End With |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Blank Rows when Pivot Table included on sheet
You can't delete or add rows to a Pivot Table. Delete all blanks before you
build the Pivot Table (I assume you are building the table with code, right). Or, in your Pivot Table you will see some down arrows, click any one and if there are blanks in there you'll see (blank) with a small check box in front of it. Un-check the box and that will eliminate the blanks. Then, turn on the macro recorder and do the same thing and turn off the recorder. Look at the resulting code. Copy/paste that code into the appropriate place in your code-sequence, and Excel will automatically do this for you next time (eliminate blanks). You may have to play with the positioning of the code a bit to get it working right, but just keep at it and you'll get it. -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Joyce" wrote: Hi, I've run code to delete blank rows which works fine. That is, it works fine until I'm deleting blank rows on a sheet which also contains a few pivot tables. I encounter an error message regarding not being able to change pivot table. Here is my code: Thanks! Dim x As Long With ActiveSheet For x = .Cells.SpecialCells(xlCellTypeLastCell).Row _ To 1 Step -1 If WorksheetFunction.CountA(.Rows(x)) = 0 Then ActiveSheet.Rows(x).Delete End If Next End With |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Blank Rows when Pivot Table included on sheet
Hi Ryan,
Thanks so much for your response. Here is my problem in more detail. I have a report that contains many pivot tables that are positioned vertically one beneath the other. There are report headings and information above the pivot tables as well. I don't want to overwrite the text between the pivot tables as the pivot may expand. At the moment, the only solution I can see is to add a bunch of rows under each pivot table so that refreshing them based on updated data and different Page Field selections won't overwrite anything. Unfortunately, though, it means I end up with many, many rows very often that remain beneath various pivot tables. I'd like to have an easy way to remove these. I had used placeholders on rows I don't want removed and run my code to delete blank rows that I've used often in other reports that don't contains pivots, but get a runtime error 1004 when try to run it in one that does. Is there a way to add something that says, in essence, "ignore pivot tables"? Any suggestions are very appreciated. "ryguy7272" wrote: You can't delete or add rows to a Pivot Table. Delete all blanks before you build the Pivot Table (I assume you are building the table with code, right). Or, in your Pivot Table you will see some down arrows, click any one and if there are blanks in there you'll see (blank) with a small check box in front of it. Un-check the box and that will eliminate the blanks. Then, turn on the macro recorder and do the same thing and turn off the recorder. Look at the resulting code. Copy/paste that code into the appropriate place in your code-sequence, and Excel will automatically do this for you next time (eliminate blanks). You may have to play with the positioning of the code a bit to get it working right, but just keep at it and you'll get it. -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Joyce" wrote: Hi, I've run code to delete blank rows which works fine. That is, it works fine until I'm deleting blank rows on a sheet which also contains a few pivot tables. I encounter an error message regarding not being able to change pivot table. Here is my code: Thanks! Dim x As Long With ActiveSheet For x = .Cells.SpecialCells(xlCellTypeLastCell).Row _ To 1 Step -1 If WorksheetFunction.CountA(.Rows(x)) = 0 Then ActiveSheet.Rows(x).Delete End If Next End With |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Blank Rows when Pivot Table included on sheet
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table - with blank rows | Excel Discussion (Misc queries) | |||
Pivot Table blank rows | Excel Discussion (Misc queries) | |||
delete all blank rows in a table | Excel Programming | |||
Delete all Blank Rows from data table | Excel Programming | |||
Macro to look for blank rows in sheet, delete the row and autofit | Excel Programming |