+ Reply to Thread
Results 1 to 5 of 5

Macro to hide rows in pivot table if values in row C&D are both 0

  1. #1
    Registered User
    Join Date
    12-13-2012
    Location
    Kalamazoo, MI
    MS-Off Ver
    Excel 2010
    Posts
    3

    Macro to hide rows in pivot table if values in row C&D are both 0

    I have browsed this forum and found a few helpful answers but nothing seems to be working for me. I have a large set of data in A1:D6000 something. I am looking for a macro that hides the full row if the values in column C & D are BOTH 0. Any help would be appreciated. Thanks.

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,140

    Re: Macro to hide rows in pivot table if values in row C&D are both 0

    hi nhouse20, welcome to the forum. try this:

    Please Login or Register  to view this content.
    to be safe, i also included that column B should not be a blank. otherwise, it will hide rows above the pivot data values. if it doesnt help, uploading a sample Excel file will enable us to help you better. to upload, press "Go Advanced" beside the "Post Quick Reply" button & click on the paperclip icon
    ideally, it should contain your desired results

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  3. #3
    Registered User
    Join Date
    12-13-2012
    Location
    Kalamazoo, MI
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Macro to hide rows in pivot table if values in row C&D are both 0

    Thanks for the help, pretty much got it all done now. I was curious, is there a way to only hide row from column A to F? This way I can input a pivot table in next to my set of data without the pivot table freaking out whenever the Macro is run. I've been trying to edit the one above to make it do this but I seem to be having difficulty, per usual.

    Thanks again.

    Quote Originally Posted by benishiryo View Post
    hi nhouse20, welcome to the forum. try this:

    Please Login or Register  to view this content.
    to be safe, i also included that column B should not be a blank. otherwise, it will hide rows above the pivot data values. if it doesnt help, uploading a sample Excel file will enable us to help you better. to upload, press "Go Advanced" beside the "Post Quick Reply" button & click on the paperclip icon
    ideally, it should contain your desired results

  4. #4
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,140

    Re: Macro to hide rows in pivot table if values in row C&D are both 0

    glad to help. you cannot hide partial columns or rows. it has to be the whole thing column or row.

  5. #5
    Registered User
    Join Date
    12-13-2012
    Location
    Kalamazoo, MI
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Macro to hide rows in pivot table if values in row C&D are both 0

    Quote Originally Posted by benishiryo View Post
    glad to help. you cannot hide partial columns or rows. it has to be the whole thing column or row.
    Sub Test()

    LastRow = Cells(Rows.Count, 1).End(xlUp).Row

    For v = 1 To LastRow
    If Cells(v, "B") <> "" And Cells(v, "C") = 0 And Cells(v, "D") = 0 Then
    Rows(v).EntireRow.Delete = True
    End If
    Next v

    End Sub

    I tried this to get the rows to delete but it gave me an error, "Unable to set the delete property of the range class"
    Any more ideas?

    Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1