Hide Unhide Rows via Macro

[Prave]

Hey folks,

So I have a problem that is beyond my capability. I have a couple questions, some more simple than others.

I have a workbook that has 500 rows. In order to be able to print the spreadsheet, I have added a Macro in that hides any rows that have a "0" in the A column. I then put an if/then formula (ex. if(isblank(A5),0) so that if the cell was blank and 0 would be there and therefore the row would be hidden.

The problem now is that I want a new row to become unhidden everytime the row above has data in it. So, if row 5 gets data put into it, row 6 would become unhidden. The problem I'm running into is that the data in column A is peoples names, and therefore each row will have a different name (i.e. different data).

1. Is there a macro to do this?

2. (This may be a really stupid question, but...) Can you run 2 macros in the same sheet?

Thanks guys, keep in mind I'm new to this...

 

[drsarao]

Welcome To MrExcel!
Do you put the 0 manually in Col A?
Where do you put if(isblank(A5),0)?
AFAIK you can't run 2 macros simultaneously.

 

[Smitty]

Welcome to the Board!

Maybe a change event:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#007F00">'   Code goes in the Worksheet specific module</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br>        <SPAN style="color:#007F00">'   Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> rng = Target.Parent.Range([A2], Cells(Rows.Count, "A").End(xlUp))<br>             <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>            <SPAN style="color:#007F00">'   Only look at that range</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>            <SPAN style="color:#007F00">'   Action if Condition(s) are met (do your thing here...)</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> LenB(Target.Value) <> 0 <SPAN style="color:#00007F">Then</SPAN><br>                <SPAN style="color:#00007F">With</SPAN> Target.Offset(1)<br>                    .EntireRow.Hidden = <SPAN style="color:#00007F">False</SPAN><br>                    .Select<br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>            <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

And yes, you can run two macros on the same sheet, but as pointed out, not simultaneously.

Hope that helps,

 

[Prave]

Drsarao,

I do not want to manually enter "0" in column A, which is why I put the if(isblank(A5),0). I then copied that formula and pasted it down the entire A column, so that all of the blank cells would have 0 in them (and therefore would be hidden.

So if you cannot have 2 macros simultaneously, I need a macro that:

* will hide rows if the A column cell is blank and
* insert a row below the last row that has information in the A column cell

Smitty - does your macro do these two functions?

 

[Centrican]

Why not just apply a filter?

 

[Prave]

Because I am pushing data into this sheet from another application. It is evaluation data (qualitative and quantitative). Column A is the name of person being evaluated. We will have up to 500 people, but I want the spreadsheet to see only the rows that have data in it.

Therefore, I need the rows that are blank to be hidden.

The reason why I am looking to have one row become unhidden beneath the last row of data is because I have a macro in place that hides all of the rows with "0" in it, but when data gets pushed into those rows, they stay hidden.

For example, we have three rows of peoples evaluation data, when we push in data for a 4th person, then I still cannot see the 5th row (because row 1 is a header row).

The data then is compiled in an adjacent sheet and put into graphs. I dont want to have to manually filter the data in order to make this happen. i.e. whatever data appears on the spreadsheet is what the graphs are populated with.

So, coming back to the two things that I would like a macro to do:

* hide rows with no data in them
* unhide one row under the last row that has data in it (if data is in row 4, unhide row five even though it will be blank).

 

[Centrican]

If you code the macro to apply the filter, it will hide all rows where column A is null, but not all rows in the spreadsheet. You'll still have all the rows after your data appearing blank, unless you actually want to have all of them contain null values (which will take up a lot of space).

It still sounds to me like a filter is the simplest option. Just unfilter, copy in the data, then apply the filter again. To know what code to write, just record a macro where you do this.

Is there any particular reason why you only want the sheet to be up to 500 rows long? If it's really important the code by Smitty will do what it sounds like you are asking for. A filter would probably be faster tho.

 

[Prave]

Thanks so much for talking this through... I feel like I'm getting closer, but I have to ask some unfortunate questions of inexperience...

To answer your question though, no it is not important that there be 500 rows, that is just a safe number that I do not foresee being exceeded over the next 5-10 years (we will not evaluate over 500 people over the next 5-10 years) and will be able to continue using this workbook.

So, the unfortunate part, I don't know how to record macros. I am very new to working with macros and most of the reason why I've gotten as far as I have is through researching and trying other code that I've seen posted on MrExcel. Any suggestions on where I might go to learn how to record a macro?

Also, from what I know about filters, there is still a component that requires a manual intervention. As I see it, with a filter, the process of creating a 'report' would look like this:

1. All Data pushed into workbook at once
2. I manually go in, drop down the filter box (all unselected or 'blank' rows become hidden) and the rows that are selected contain the data that I will populate the adjacent graphs.

What I want the process of creating a 'report' to be is all in one step:
1. All Data is pushed into the workbook, all of the 'blank' rows become hidden, and the graphs are populated by the data that is showing.

Note: I am looking for different types of 'reports' (i.e. individual reports from different people, and group reports from different groups).

Are you saying that with a filter, there still has to be that manual intervention in the process of creating a report or can a macro that engages a filter do this? Can I have macro that engages the filter to select only the rows that have data?

Thank you SOOOOOO much, this is such a huge help and I really appreciate you patience with my inexperience.



1. All Data is pushed into work book at once and

 

[Prave]

Disregard that last line that is the beginning of an earlier line "1. All Data is pushed..." that was a typo.

Thanks so much again!

 

[Smitty]

Smitty - does your macro do these two functions?

I don't know, did you try it?