Hide, Unhide Rows and count!?

[wjsok85]

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

[BigBas]

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.

[stanleydgromjr]

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

[wjsok85]

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

[wjsok85]

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

[stanleydgromjr]

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

[wjsok85]

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

[stanleydgromjr]

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

[wjsok85]

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

[stanleydgromjr]

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

[wjsok85]

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