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

 

[Smitty]

Unfortunately, I'm outta' here for the 2 hours drive home.

I'll try to checck back in the morning.

 

[Centrican]

Did you manageto get Smitty's code to work?

Sorry for not replying again, I also went home for the weekend.

FYI to record a macro, go to Tools > Macro > Record Macro


This is the filter code:

Sub FilterMacro()
'Turns off the autofilter
Rows("1:1").Select
Selection.AutoFilter

'Turns on the autofilter and removes blanks
Rows("1:1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="<>"
End Sub

This assumes you already have a filter applied, so when you run it for the first time it needs you to add the filters manually. The code is removing the filter, which will display all rows, then applying the filter again on row 1, then sets the autofilter to filter out rows that have blank cells in column 1.

It should do exactly what you want and is much easier for you to understand and modify yourself. Changing the 'Field' will change what column it checks for blanks (null values).

If you want to paste in data, do this between the removal of the filter and the application of the new filter. You might have to delete all the data on the sheet if you could end up pasting less rows than you had the last time you did it.

Unless I've still not understood what you are trying to do of course...

 

[Prave]

Thanks for your reply. This is an interesting way of going about this problem. We did get Smitty's response to work on the first 2 worksheets, as I was asking!

I am now trying to figure out the same code for the 3rd sheet, but instead of the data rows starting at A2, they start at Cell B22. I still want the same thing to happen, that if there is not any data then to hide the rows, but if there is data to be unhidden AND insert one blank row underneath the last row with data.

Can you help with this? This would be worksheet 3.


Thanks Cent!

 

[Prave]

So I am having some problems completing this workbook. The first two worksheets are perfect, then the 3rd worksheet is where I'm having problems.

The entire 3rd sheet has functions in it that averages numbers from the 1st two sheets.

I want the exact same thing to happen on the third sheet, where if there is no data, then I want the rows to be hidden (starting with A2) and if there is data I want it to insert one row underneath the last line with data.

Below is the macro that I'm working with, this is that macro from the first two sheets and it should be the exact same right? However, whenever the macro kicks in, it ends up unhiding every row in the entire sheet (500) and because of this having a run time error. I am thinking that the problem is that the cells in the 3rd sheet have functions in them which is keeping the macro from recognizing them as being blank and therefore is unhiding all of the rows. Any thoughts on how to help?

It is a SelectionChange macro, if that helps:

Private Sub Worksheet_Change(ByVal Target As Range)


End Sub
Sub HideRow()

End Sub

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