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...

 

[Prave]

I just tried it, with no data on the sheet and nothing happened. So now there is just a blank 500 rows, with none of them being hidden.

Do I need to adjust any of the information in your script to make it specific to the worksheet?

 

[Smitty]

The code assumes that you have a header row and at least 1 cell of data below it.

 

[Prave]

Ok, just so I'm clear...I push data into row 2 and then the macro will hide all of the other rows?

 

[Prave]

I just put 4 rows of data into the worksheet (rows 2,3,4,5) and went back and "ran" the macros that you sent me (that I have not touched other than simply clicking copy and paste).

Nothing happened. There is four rows of data and then 495 more rows that are blank, that I would prefer to be hidden.

Does this make sense?

 

[Smitty]

OK, let's say you have rows 3-500 manually hidden. If you enter data into row 2, row three will be unhidden. If you then enter data in row 3, row 4 will be unhidden, and so on.

 

[Prave]

Yes, that's exactly what I need! This is not happening though. I copy/pasted your macro into the sheet 1 box, then I closed VB and then clicked "run" the macro and rows 7- 500 should have become hidden, correct?

 

[Smitty]

No. What I posted is a change event, you don't run it, it runs automatically based on data entry in column A.

As I mentioned in my last post, it assumes that you have manually hidden rows 3-500 to start with.

 

[Prave]

So I think I understand what the problem is. Your macro does work perfectly! Thank you!

The problem is that nobody is going to be manually entering data into the spreadsheet, it is going to be populated by data that is sent to it through another application. Because of this, it is not recognizing your macro.

What is happening is, we sent four rows of data through the app into the spreadsheet with your macro. The data is put into the spreadsheet, however only that first row is showing (Row 2, which was left blank). When we went to cell A2 and retyped what was in that cell and hit 'enter', row 3 appeared. And as it turns out, the data is all there, but the rows are not becoming "unhidden".

Do you think there is a way for the macro to recognize whether or not data is in the cell with out someone manually entering the data into it (and THEN engaging in what your macro says to do)?

Thanks so much, this is extremely helpful.


P

 

[Smitty]

Sure, you need to convert the code to a Calculate event, which will fire whenever the sheet calculates. Unfortunately, it doesn't support the Target method though, so you'll need a workaround.

 

[Prave]

Ok, thanks to your help we got the first step figured out! Thank you very much!

The next (and hopefully) last step is to run the same macro on Sheet3 (which compiles data from Sheet1 and Sheet2 - and therefore no data is manually entered) in order to hid all of the rows that have no data accept for the one row beneath the last row of data.

The header then starts on cell B21 and then the first cell to have data in it would be B22. The B Column has the same names as the A columns in sheet 1 and sheet 2.

All of the rest is the same though, if it is blank, I want the entire row hidden.

The catch is that the B column has a Fn in it to be able to input the names from Column A from Sheet 1. I think it is reading the function as in "having data" and therefore is populating all 500 lines.

Any thoughts on how to get around this?

You rock Smitty!