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
0
Utf
3/21/2010 7:35:01 PM
excel.programming 6508 articles. 2 followers. Follow

3 Replies
1405 Views

Similar Articles

[PageSpeed] 26

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
0
Utf
3/22/2010 2:40:01 PM
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
0
Utf
3/22/2010 3:46:02 PM
Sounds somewhat complex, but equally interesting.  I have some free time in 
the next day or so to look at this.  If you send me a before and after 
example, I'll try to reply back with a solution within the next day or so.  
Thanks!!
ryguy7272@hotmail.com

-- 
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Joyce" wrote:

> 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
0
Utf
3/23/2010 3:04:06 PM
Reply:

Similar Artilces:

Special Pasting a work book with many sheets and formulas
I have a workbook with many sheets that all have formulas and links to other data. I want to save the workbook as another name with all the worksheets keeping the values only (no links or formulas). Is there a quick way to do this for everysheet without having to special paste every sheet in the workbook. So can I save everysheets data values at workbook level. See this page for a code example http://www.rondebruin.nl/values.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "lex63" <lex63@discussions.microsoft.com> wrote in message news:ED708...

Proper Table(s) Layout
I have created a couple of Dbs which the table structure seemed to simply fall into place, it was just logical in my head. That said, I am working on a new db and for some reason I am doubting myself and wanted a second opinion. The db is basically a contract db to input all the info, and there is a lot of info, for each contract. Where I am 'lost' is the fact that the contracts are broken into categories: clients, components,engineering, warantee... For all of the components (with the exception of clients) there are a number of fields but only 1 entry per contract....

Delete dead mailbox from active directory
We have four exchange servers - two E2K and two E2k3. One of the E2k server just died due to corrupt array control. We had no mailboxes or anything else on that server. One of our administrator must have created one mailbox on that server by mistake and he never realized that until now. server died and we deleted that mailbox from active directory but, when i try to remove the server from system manager i get message saying can't remove because there are mailboxes associated with this information store. Is there any other way i can remove that server from active directory or s...

Excel and some disappeared sheets
Anybody has the same or similar experiences : - I worked with excel file / closed as usually - new day opened and unfortunatelly some very important sheets disappeared / why ? Don=B4t know - I had about 7-9 sheets just now only 3 ones are there but not so important as another ones - the file has original size / I quickly looked into file by normal text editor only for reading and all relevant data are there, however if I open file by Excel, NO original sheets or data are there. - this file is VERY IMPORTANT for me - why happened ? Don=B4t know since I ask anybody for help Thanks in advan...

How do you delete an Assembly Item?
I have an assembly item code, and I want to delete it to make it inactive. I cannot find any help on this, nor can I actually FIND the assembly item. Please help!! This is a multi-part message in MIME format. ------=_NextPart_000_0461_01C6F844.55DE89B0 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Nutribodies, You can't make an Assembly inactive as its not really an item, just a = reference to a group of items. You can delete it though. One way of finding items is by their Item type. Click on the Item Type = column header and th...

Table link documentation
I am having trouble trying to locate A/P check data that has project related costs. I found the check data but it is does not indicate the projects, I found the project data but can not determine thye logical link between the two tables, I may be using the wrong tables the tables I am using are PM80500 and PA31102. Is there any documentation of how all the tables in the system are logically link. I am trying to write reports in MS Access, but there are 1500+ tables in GP (version 10) -- Dave F In an effort to find the correct table you can do a number of things (believe me I do)....

Can you delete Business Alerts?
I cannot see any way to delete Business Alerts, can someone tell me how? I am using GP 8.0 -- Sheri Salomone THANKS! Try going to Cards --> System --> Business Alerts. -- Charles Allen, MVP "Sheri Salomone" wrote: > I cannot see any way to delete Business Alerts, can someone tell me how? > I am using GP 8.0 > -- > Sheri Salomone > THANKS! woo hoo! Thank you! -- Sheri Salomone THANKS! "Charles Allen" wrote: > Try going to Cards --> System --> Business Alerts. > -- > Charles Allen, MVP > > > > "Sheri Salo...

Duplicate Rows
I have an extract from a student information system in Excel that looks like this. Student Class Grade Quarter John Chemistry 70 1 John Chemistry 80 2 John Math 95 1 John Math 100 2 Alice Chemistry 67 1 Alice Chemistry 47 2 Alice Math 88 1 Alice Math 85 2 What I would like is this: John 70 80 95 100 Alice 67 47 88 85 However, since there are hundreds of students, this would be an extreme pain to do by hand. Is there any built-in formula or function in Excel that can do this? What is it that you actually want to do? (The best approach depends on what your desired end r...

Add rows automatically? Accordion
Is there a way to automatically add/show rows that have data? I have a data entry sheet. Then I have a report. The report pulls data from the entry sheet. If there is no data for a specific line/row item, is there a way to automatically hide or not show the row(s) with no data? Thanks Thanks can I have more than one autofilter on a sheet? Sloth wrote: > Use the filter function > Select the data and click on... > Data->Filter->Autofilter > This should make an arrow appear at the top of the data (in the header row). > click the arror and select "Nonblanks"....

Duplicate record in RM tables
We experienced an issue in Apply Sales Document that may have caused a duplicate record somewhere. We found this when running Paid Sales Transaction Removal and received this message: Violation of PRIMARY KEY contraint PKRM3101. Cannot insert duplicate key in object RM30101. I ran the RM duplicate tool found in the automated help area of this website and found the following: --- Begin copy here ---- Duplicates between RM Open and RM History Document #: 07-003021-17 Customer #: 079100 RMDTYPAL #: 7 --- End copy --- It looks like the duplicte tool also logs the qu...

Can the data in a chart table be right justified?
Ecxel 2003 and previous versions of the product center the data in the data columns. Can the data in the columns of a chart table be right justified? In article <DABF738B-6C0D-458B-B082-FA9BD8F126A7@microsoft.com>, =?Utf- 8?B?c2FtIGVhZ2xl?= <sam eagle@discussions.microsoft.com> says... > Ecxel 2003 and previous versions of the product center the data in the data > columns. Can the data in the columns of a chart table be right justified? > Have you tried to format the table? If yes, and you haven't been successful it is probably because XL allows very limited cust...

140 MB file went to 5.08 MB after editting 1 table
Hello All - I need some ACCESS insight...please... Several years ago, I built an access db to track my business scheduling and accounts payable/receivable. So this database is EXTREMELY IMPORTANT TO ME. The file has grown to 140 MB. Today I made a copy of the file and then edited my calendar table. I removed all columns which had 2006 data (72 totals columns) - the table had about 144 columns originally. I then added 72 columns with 2008 headers. These columns are now blank since I have not added any 2008 data yet. Afterwards, I looked around and everything looks good - my 2007 data is the...

Limit on Exchange 2003 version included with SBS 2003
Can anyone tell me if the version of Exchange 2003 included with Small Business Server 2003 has the 16GB limit that exists in the Exchange 2003 standard edition? Yes. >-----Original Message----- >Can anyone tell me if the version of Exchange 2003 >included with Small Business Server 2003 has the 16GB >limit that exists in the Exchange 2003 standard edition? >. > ...

Coloring a row
I have a spreadsheet and I want to have cells colored from column A to K if cell h is not blank. So if h3 has a date in it I want A3:K3 to be say light blue. This is for Office 2003. I can do it with conditional formating in 2007, but my work place doesn't have 2007. I did use column L and put an if statement to give a true or false in the cell depending on if the cell in col. h was empty or not. Any ideas how to get this to work? Hi John This sort of thing will work in 2003 conditional formating. In Cell A3 go to Format - conditional formattting. Formula is Paste...

Pivot Table Defaults
In the pivot table field list, whenever I create a new pivot table and I am inserting fields into the value area, I generally get as default field setting the 'Count' value. Is there a way to format the spreadsheet to make Excel recognize the data as all numbers so it defaults to the "Sum" function as opposed to "text"? Hi The rule that the PT Wizard adopts is, If all the values in the field being added to the data area are Numeric, then it uses Sum. If any of the values are Text or BLANK, then it uses Count. It sounds as though you have defin...

Like a pivot table
Hello every body I'm first time requesting in this group, so I opologize in advance for any mistakes or something annoying I repeat what I have sent before 10 min because I see it unclear when it goes to news group If any one can help me I'm working with data which most of it comes like a table with feilds as columns and records as rows. I want it to be as many rows with each feild an example what is exist name age Joining Date Tele John 20 Jun-90 4321251 Iqbal 30 Jul-95 6583752 George 40 Sep-85 7843125 What I wa...

Can't re-enter a previously deleted User ID
We changed the spelling of a User ID (applewicks to appelwicks) and then deleted it (since he couldn't remember his password and the button for password was greyed out so we couldn't change it.) And now we can't re-enter the same user ID even though it doesn't appear in the window any longer. Here is the error we get: ODBC SQL server driver: The log in appelwicks already exists. Thanks! I believe you have to delete the old ID through Enterprise Manager as well. "cliffs" wrote: > We changed the spelling of a User ID (applewicks to appelwicks) and then ...

how can I do a lookup into another sheet?
how can I do a lookup into another sheet? thanks ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ Try something like =VLOOKUP(123,Sheet2!A1:B10,2,FALSE) -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "melawaisi" <melawaisi.ws67m@excelforum-nospam.com> wrote in message news:melawaisi.ws67m@excelforum-nospam.com... > > how can I do a lookup into another sheet? > thanks > > > ------...

Auto Delete the e-mail
Hi all We are using Microsoft Exchange 2003 and Outlook 2003. One mail box calls "Sales", i configured the achieve function for this mail box, this function will automatically delete mail if the mail is order than 7 days. But this function is not running if the outlook closed, is it possible this function running on server only, no need to open Microsoft Outlook for running the achieve function in client PC ? Philip In news:uApBq0j8FHA.2800@TK2MSFTNGP10.phx.gbl, Philip Tsang <philip@sib-infotech.com> typed: > Hi all > > We are using Microsoft Exchange 2003...

mysession & deleting a line item on the pos screen
I want to automatically have my dll delete a line on the pos screen. So I have tried to do a myssion.transaction.entries(1).Delete and other sorts of things. Does anyone know of a way to accomplish this? Thanks in advance for any help on this. Hi, Use code like this: For Each item In mysession.Transaction.entries If FindItem(item.item.id, item.quantity, item.item.description) Then mysession.Transaction.entries.remove(item) End If Next "ClothingStore" wrote: > I want to automatically have my dll delete a line on the pos screen. ...

Zero to appear as blank
I have a VLOOKUP formula below. When the value is zero, I want the result to be blank. What do I do. =IF(A10="","",VLOOKUP(A10,detail,2)) Thnx Prakash..... =if(a10="","",if(vlookup(a10,detail,2)=0,"",vlookup(a10,detail,2))) Prakash wrote: > > I have a VLOOKUP formula below. When the value is zero, I want the result to be blank. What do I do. > > =IF(A10="","",VLOOKUP(A10,detail,2)) > > Thnx > Prakash..... -- Dave Peterson ec35720@msn.com In addition to modifying your formula as Dave suggeste...

Fulfillment type orders
We have orders at the initial status (just entered) and the options to allow delete documents and void documents are enabled on the Sales Order Type. However, we can't get a void or delete button unless we move the fulfillment order through the confirm process to an "Invoice" to get a Delete? What's up with this? Very annoying. Jana, I know it is subtle and wish there was a better way to display it, but the Void option only applies to Invoice type IDs and not Fulfillment Order type IDs ("Void Invoice Documents" vs. "Delete Documents"). Since both t...

Freeze the side column/top row & scroll others
what is the function to set (lock in or freeze) the first column and / or top row of a spreadsheet, so the words and numbers remain in the same place as you scroll the other columns and rows. (so you can add more columns..yet keep the main information in the first column/row) Freeze Panes..... In older versions of Excel, it is under Window. In 2007 version of Excel, it is under View. You first select a cell, then activate the command. Excel uses the selected cell's upper left corner to define the freeze point. Play with it. You can also Unfreeze panes that were fro...

Duplicating one Field from One table to Another
Hi - I have two tables - one position, one personnel - which has a 1-to-many relationship (1 position record to many personnel records). The department had a new requirement which made it necessary to change some coding (I inherited this). I'm using tab forms so that when a position is pulled up, you can click on the tab that has the personnel information (if there is any). There is a button on the Personnel form that allows the user to add a new Personnel record. Since I am using an Auto-number field in the Position table (which doubles as the PK) the functionality is fine. Wh...

unable to Delete shape
I am using Visio 2000 (6.01245), if i select a shape and press delete button on keyboard then shape dosent delete.Can anyone tell what is this problem.How can i resolve this problem. Kindly Help Asif Does object have gray boxes in corners when selected? If so, object has "protection". Menu/format/protection/uncheck/OK "Asif" <Asif@discussions.microsoft.com> wrote in message news:289D3FD5-21DC-4584-9F0E-36CC56FF25B2@microsoft.com... >I am using Visio 2000 (6.01245), if i select a shape and press delete >button > on keyboard then shape dosent dele...