+ Reply to Thread
Results 1 to 11 of 11

Hide, Unhide Rows and count!?

  1. 04-02-2009, 08:08 AM #1
    wjsok85 is offline
    Registered User
    Join Date
    01-13-2009
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2003
    Posts
    52

    Hide, Unhide Rows and count!?

    I'm stumbling on something that possibly quite simple.

    I have a code that will hide all rows that have a "-" in Colum A, leaving me with the useful data.

    However, this data is pulled from a front sheet that has data added to it.

    What I would like my code to do is to relook at all the hidden cells, and if their value has changed from "-" to what ever the data may be then to unhide that row, displaying the data.

    The code I'm using currently is:
    Please Login or Register  to view this content.
    What I would also like to achieve is a simple way to then count the amount of rows with data in them and display that figure in Cell D2 for example. So that I know at a glance how many clients I have in that list once compacted.

    Any ideas?

    Thanks
    Last edited by wjsok85; 04-05-2009 at 10:30 AM. Reason: Solved
    Reply With QuoteRegister To Reply
  2. 04-02-2009, 09:28 AM #2
    BigBas is offline
    Forum Expert
    Join Date
    01-12-2007
    Location
    New Jersey
    Posts
    2,127

    Re: Hide, Unhide Rows and count!?

    How about just adding another condition to your IF statement. You already have a condition to hide rows if they are "-", so let's add a condition to unhide rows that are NOT "-"

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  3. 04-02-2009, 09:56 AM #3
    stanleydgromjr is offline
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: Hide, Unhide Rows and count!?

    wjsok85,

    To undide all rows that are hidden.

    Please Login or Register  to view this content.

    Formula in cell D3 to count only visible rows in range A4:A2500
    =SUBTOTAL(103,A4:A2500)


    I have another idea so that you may not have to loop.

    What I would like my code to do is to relook at all the hidden cells, and if their value has changed from "-" to what ever the data may be then to unhide that row, displaying the data.
    Are the cells displaying the "-", text or formulas?

    If they contain formulas, what is the formula?


    Have a great day,
    Stan
    Last edited by stanleydgromjr; 04-02-2009 at 10:00 AM.
    Have a great day,
    Stan

    Windows 10, Excel 2007, on a PC.

    If you are satisfied with the solution(s) provided, please mark your thread as Solved by clicking EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.
    Reply With QuoteRegister To Reply
  4. 04-02-2009, 10:02 AM #4
    wjsok85 is offline
    Registered User
    Join Date
    01-13-2009
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2003
    Posts
    52

    Re: Hide, Unhide Rows and count!?

    Hi Stan,
    the formula is,
    =IF(Scripts!B5="L1 Brochures",Scripts!A5,"-")

    But have run into bigger problems, as this formula is sloppy, if I insert cells it messes up everything and I have to copy the formula down from cell A4 so that it refreshes all of the formulas.

    Thanks

    Wil
    Reply With QuoteRegister To Reply
  5. 04-02-2009, 10:07 AM #5
    wjsok85 is offline
    Registered User
    Join Date
    01-13-2009
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2003
    Posts
    52

    Re: Hide, Unhide Rows and count!?

    Hi BigBas,

    That was what I was trying to work towards, but missed the 'ElseIf' which must have been where I was going wrong

    But like I said with the formula being the way it is, when I insert a new row (accross all sheets) the formula is not present in the new cell, with the correct cell references to pull back the data and give the macro someting to unhide

    Wil
    Reply With QuoteRegister To Reply
  6. 04-02-2009, 10:14 AM #6
    stanleydgromjr is offline
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: Hide, Unhide Rows and count!?

    Wil,

    Can you attach your workbook (see below "Manage Attachments")?

    There is a Worksheet Insert Row Event that we can probably adjust to put the formula in the row that was inserted:
    Worksheet Insert Row Event - Tom Urtis
    http://www.mrexcel.com/archive2/72100/83615.htm


    Have a great day,
    Stan
    Reply With QuoteRegister To Reply
  7. 04-02-2009, 10:30 AM #7
    wjsok85 is offline
    Registered User
    Join Date
    01-13-2009
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2003
    Posts
    52

    Re: Hide, Unhide Rows and count!?

    Hi Stan,

    Any help would be appreciated! This is taking up alot of my time, and just gets more a more frustrating!

    As you can see from the data valiadtion, there are many more sheets that I work with (had to delete some of the calcs and sheets as it was making the attachement too large)

    Thanks

    Wil
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 04-02-2009, 10:43 AM #8
    stanleydgromjr is offline
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: Hide, Unhide Rows and count!?

    Wil,

    In the three sheets you have deleted the data from column B thru columns F or G.

    Are you only interested in filling the formulas in column A, where rows are inserted?


    Have a great day,
    Stan
    Reply With QuoteRegister To Reply
  9. 04-02-2009, 10:47 AM #9
    wjsok85 is offline
    Registered User
    Join Date
    01-13-2009
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2003
    Posts
    52

    Re: Hide, Unhide Rows and count!?

    Hi,

    The 3 sheets L1, L2, L3 only have data present in coloum a, the client name, the rest of the comoums are obsolete, so yes I would want the formula to copy down coloum A only

    Thanks

    Wil
    Reply With QuoteRegister To Reply
  10. 04-02-2009, 05:43 PM #10
    stanleydgromjr is offline
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: Hide, Unhide Rows and count!?

    Will,

    See the updated workbook "Example_ClientDataRemoved(1) - wjsok85 - SDG.xls".

    I have added the formulas in cells D2 "=SUBTOTAL(103,A4:A2500)". You will see a count of only visible rows in Range A4:A2500.

    I adjusted the "HideRows" macro.

    I did not do anyting with your "InsertRow" macro. I do not understand your requirements.


    Have a great day,
    Stan
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  11. 04-05-2009, 10:29 AM #11
    wjsok85 is offline
    Registered User
    Join Date
    01-13-2009
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2003
    Posts
    52

    Re: Hide, Unhide Rows and count!?

    Stan,

    Thanks for the help with the count and insert row macro. With regards to the insert row macro, I think that its becoming overly complicated. Im going to have a rethink on the spreadsheet!

    Thanks for all your help!

    Wil
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1