Need help creating macro to hide rows based upon whether 1 of 3 rows have data

[BigEdJr]

Hi!

I am new to this forum, but I have learned a lot already. So thank you for all the help you have given already!

I am trying to create a macro that will hide rows that do NOT have an x or an X or it might be other data like a number or something else in 1 of 3 columns.

Basically this is a sign up type spreadsheet, where people are signing up for 1 of 3 time slots on a specific day. There are specific days so eventually I will want to have a macro that will show the rows for the people that have signed up for that day and hide all the others.

For example, I will want to click on the date 3/1/2014 and a macro fill run that shows only the rows for people who have signed up for that date.

I hope that makes sense.

I have attached a sample spreadsheetHide Rows Testing.xlsm

Thank you again to all of you who share so much experience!

[BigEdJr]

In searching and trying stuff on my own I copied and edited the following code, which work on checking Column G. That's great. I just need to figure out how to have it search columns H and I also and if there are cells with data then will not hide them.

Sub Hide_me()
Dim MyRange, MyRange1 As Range
LastRow = Cells(Rows.Count, "G").End(xlUp).Row
Set MyRange = Range("G7:G" & LastRow)
For Each c In MyRange
    If IsEmpty(c) Then
        If MyRange1 Is Nothing Then
        Set MyRange1 = c.EntireRow
        Else
        Set MyRange1 = Union(MyRange1, c.EntireRow)
        End If
    End If
Next
If Not MyRange1 Is Nothing Then
MyRange1.EntireRow.Hidden = True
End If
End Sub

Any thoughts?

[JOHN H. DAVIS]

Maybe:

Sub BigEdJr()
Dim y As Long
Dim rcell As Range
Application.ScreenUpdating = False
y = ActiveSheet.UsedRange.Columns.count + 1
For Each rcell In Range("H2:J" & ActiveSheet.UsedRange.Rows.count)
    If rcell.Value = "x" Then Cells(rcell.Row, y).Value = "x"
Next rcell
Range(Cells(2, y), Cells(ActiveSheet.UsedRange.Rows.count, y)).AutoFilter 1, "<>x"
Application.ScreenUpdating = True
End Sub

[BigEdJr]

thank you John for your response. I tried using the code you gave and edited it a little, but doesn't seem to filter on the columns like I was hoping. It also locks up spreadsheet so I cannot undo any of the changes it makes.

Any advice on how to use this code or what it is doing?

Thanks again!

Ed

[JOHN H. DAVIS]

thank you John for your response. I tried using the code you gave and edited it a little, but doesn't seem to filter on the columns like I was hoping. It also locks up spreadsheet so I cannot undo any of the changes it makes.

Any advice on how to use this code or what it is doing?

Thanks again!

Ed

The code doesn't make any changes perse. It adds an x in the next column with data and filters on that. Just turn your autofilter off.

[BigEdJr]

BTW: I like the way my first code works, but I only get it to search on column G. I am not sure how to make it search through G, H and I. If I could get that done it would be perfect for my needs.

Ed

[alansidman]

Post your code that you edited so that it can be analyzed.

[BigEdJr]

Here is the code after my changes:

Sub BigEdJr()
Dim y As Long
Dim rcell As Range
Application.ScreenUpdating = False
y = ActiveSheet.UsedRange.Columns.Count + 1
For Each rcell In Range("G7:I" & ActiveSheet.UsedRange.Rows.Count)
    If rcell.Value = "x" Then Cells(rcell.Row, y).Value = "x"
Next rcell
Range(Cells(2, y), Cells(ActiveSheet.UsedRange.Rows.Count, y)).AutoFilter 1, "<>x"
Application.ScreenUpdating = True
End Sub

All I did was try to make it work starting at the filed I wanted, G7. But it is not filtering rows that have nothing in the G, H, or I columns. In fact it looks like it is filtering rows that have something in all of those fields.

I would like to try and filter or hide rows based on whether there is something in column G, H or I. For example setting a range of G7 through I7, if it is blank hide it, if it has a value in one or more of those cells leave it.

I will set up a query for each date on my spreadsheet, so the next set would be J7 through L7 and each of these sub procedures would run after a button was clicked.

I am going to create a procedure that unhides or clears the filter before moving on to the next button etc.

Thanks guys!

Ed

[JOHN H. DAVIS]

Try:

Sub BigEdJr()
Dim y As Long
Dim rcell As Range
Application.ScreenUpdating = False
y = ActiveSheet.UsedRange.Columns.Count + 1
For Each rcell In Range("G7:I" & ActiveSheet.UsedRange.Rows.Count)
    If rcell.Value = "x" Then Cells(rcell.Row, y).Value = "x"
Next rcell
Range(Cells(7, y), Cells(ActiveSheet.UsedRange.Rows.Count, y)).AutoFilter 1, "<>x"
Application.ScreenUpdating = True
End Sub

[BigEdJr]

It is doing the same thing. I attached a couple of screen shot below.

Not sure what is going on.

Before:
Before.jpg

After:
After.jpg

[JOHN H. DAVIS]

I can't duplicate your results? One the sample you provided I Get - Name1, Name2, Name3, Name8, Name9, Name11, Name12 & Name13 showing. From my understanding that's what it should be. Here is my last offering.

Sub BigEdJr()
Dim y As Long
Dim rcell As Range
Application.ScreenUpdating = False
Columns(ActiveSheet.UsedRange.Columns.count + 1).Clear
y = ActiveSheet.UsedRange.Columns.count + 1
For Each rcell In Range("H2:J" & ActiveSheet.UsedRange.Rows.count)
    If rcell.Value = "x" Then Cells(rcell.Row, y).Value = "x"
Next rcell
Range(Cells(7, y), Cells(ActiveSheet.UsedRange.Rows.count, y)).AutoFilter 1, "<>x"
Application.ScreenUpdating = True
End Sub

[BigEdJr]

Thank you John. I am sorry about the confusion I caused with my separate spreadsheet. I had tried to hide some confidential data in my original spreadsheet and inadvertently deleted a couple of rows and did not send the whole thing etc. So thank you for putting up with me and your continued assistance.

What it looks like is happening with your code John, is that it is filtering ALL rows by data that is in ALL the columns. That is great I think I am half way there with that. So Thank you very much for that.

What I am hoping to figure out is that I want to filter on just a specific set of columns, like G:I under the date 3/1/2014 and then clear the filter and filter on the columns under 3/2/2014 which would be J:L etc.

I am trying research how everything works and not just ask for a finished solution. To that end can someone explain how the UsedRange property works? I am looking everywhere but cannot find a good description. Can it be defined as to which columns are designated as part of the filter etc? So how do I set it up to filter on just G through I? Then I will create other subs for the different needs.

Thank you again for everyone who has taken their time to help. I really do appreciate it.

Ed

[JOHN H. DAVIS]

Ed:

Sorry I was the one who was confused.

To remove Filters.

Sub Ed2()
Activesheet.autofiltermode = false
End sub

This uses a Helper cell BC1 to advance the filters. In BC1 or another cell of your preference type in 7.

Sub BigEdJr()
Dim y As Long
Dim z As Long
Dim rcell As Range
Application.ScreenUpdating = False
z = Range("BC1").Value
Columns(ActiveSheet.UsedRange.Columns.count - 1).Clear
y = ActiveSheet.UsedRange.Columns.count + 1
For Each rcell In Range(Cells(6, z), Cells(ActiveSheet.UsedRange.Rows.count, z + 3))
    If rcell.Value = "x" Then Cells(rcell.Row, y).Value = "x"
Next rcell
Range(Cells(5, y), Cells(ActiveSheet.UsedRange.Rows.count, y)).AutoFilter 1, "<>x"
Range("BC1").Value = z + 3
Application.ScreenUpdating = True
End Sub

Probably better methods though.

[BigEdJr]

Thank you again Jason. It just does not seem to be doing what I need and it was adding some weir stuff at the end of the columns. But thank you for your time and effort.

I would like to try using this code in some way:

Sub Hide_Me()
Dim MyRangeG, MyRangeG1 As Range

LastRow = Cells(Rows.Count, "G").End(xlUp).Row

' If I set it like this it filters on just column G
Set MyRangeG = Range("G7:G" & LastRow)

'If I set it like this then it checks to see if ALL the fields
'in those 3 columns have data and hides the ones with only one field containing data
Set MyRangeG = Range("G7:I" & LastRow)

For Each c In MyRangeG
'   I feel like there needs to be login here to have it check each field for data
'   but use an OR statement of somekind so that if only one field contains data it will not hide it
    If IsEmpty(c) Then
        If MyRangeG1 Is Nothing Then
        Set MyRangeG1 = c.EntireRow
        Else
        Set MyRangeG1 = Union(MyRangeG1, c.EntireRow)
        End If
    End If
Next
If Not MyRangeG1 Is Nothing Then
MyRangeG1.EntireRow.Hidden = True
End If

End Sub

As the comments say it partially is doing what I like, but I cannot figure out the logic to hide only the rows that have no data in any of the 3 columns. At this time it is doing an all or nothing kind of thing.

How can I use and OR type of statement to tell it to treat each cell individually?

I also attached the spreadsheet I am working with now Hide Rows Testing B.xlsm

Thanks,
Ed