VBA to hide rows based on cell content

RosieRose

Board Regular
Joined
Jul 29, 2014
Messages
75
Hi there, I am trying to write a VBA to hide and entire row if any of the cells in four columns ( E,H,K and N) greater than -1 and small than 1. I have written the following code:
Code:
Sub Report()
Dim i As Integer
Dim Rows As Long
For i = 7 To 37
    If Cells(i, 5) > -1 < 1 And Cells(i, 8) > -1 < 1 And Cells(i, 11) > -1 < 1 And Cells(i, 14) > -1 < 1 Then
    ActiveCell.EntireRow.Select
    Selection.Hidden = True
    End If
Next i
End Sub
I am getting error message 1004: "Unable to set the hidden property of the range class. Any help would be greatly appreciated!
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Code:
Sub report()

    For i = 7 To 37
        For j = 5 To 14 Step 3
            If Cells(i, j) > -1 And Cells(i, j) < 1 Then
                Cells(i, j).EntireRow.Hidden = True
                GoTo rowHidden
            End If
        Next j
rowHidden:
    Next i


End Sub
 
Upvote 0
wow, thanks for the quick reply! This is almost there. Unfortunately this hides every row. I guess I could have explained this the opposite way: If any of the cell contents in columns E,H,K,N are smaller than -1 or greater than 1, I want the entire row to be visible and to hide the rows whose same column contents are greater than-1 and smaller than 1. Thank you.
 
Upvote 0
Code:
If Cells(i, j) > -1 And Cells(i, j) < 1 Then

That's what this does. If the cell is > -1 AND < 1 then hide them. So if the cell is -.5, hide. If the cell is .8, hide. If the cell is 2, don't hide. Isn't that what you're looking for?
 
Upvote 0
yes, this is what I want it to do. However, when I ran the VBA. it hid every row. nothing was left visible
 
Upvote 0
Hi there, after testing it a little bit. I have realized when this is not working. For some reason the code is hiding the rows if any of the columns are <-1 >1. and only remaining visible if ALL the columns are >-1<1. I would like the opposite to occur. For the row to be visible if ANY of the columns are >-1<1 and to hide if ALL the columns in that row contain values that are <-1 >1.thank you again for your help
 
Upvote 0
Let's try to be absolutely clear.

You want:

Rows visible: if any of the column values, within that row, are between -1 and 1. (ie -.5, 0,.68, etc)

Rows not visible: if all of the columns, within that row, have value that are outside of -1 and 1. (ie -2, -3, 1.12,4, etc)

Are -1 and 1 to be included in the not visible? ie( value<=-1 or value >= 1)
 
Upvote 0
Here's a modification to NeonRedSharpie's code:

Code:
Sub report()

Dim Cntr As Integer




    For i = 7 To 37
        Cntr = 0
        For j = 5 To 14 Step 3
            If Cells(i, j) < -1 Or Cells(i, j) > 1 Then
                Cntr = Cntr + 1
            End If
            
        Next j
        If Cntr = 4 Then
          Cells(i, j).EntireRow.Hidden = True
        End If
    Next i




End Sub
 
Upvote 0
thanks Schoe1da, it works beautifully, except for it is doing the opposite of what I need it do do. It is hiding all rows with the column values greater than 1 or smaller than -1, and keeping all the values in between -1 and 1 visible. When I tried to flip the signs:
Code:
Sub report3()

Dim Cntr As Integer
    For i = 7 To 37
        Cntr = 0
        For j = 5 To 14 Step 3
            If Cells(i, j) > -1 Or Cells(i, j) < 1 Then
                Cntr = Cntr + 1
            End If
            
        Next j
        If Cntr = 4 Then
          Cells(i, j).EntireRow.Hidden = True
        End If
    Next i
End Sub
Every row was hidden.
 
Upvote 0
The reason you can't just flip the signs is because EVERY number in the known world is either larger than -1 or smaller than 1. (- infinity, 1] and [-1, infinity) represent the entire venn diagram.

earlier you said:

hide if ALL the columns in that row contain values that are <-1 >1.thank you again for your help

Pick one of these scenarios to hide a row:

-2, 5, -2, 3
0, -.5, .5, 0
-2, .5, -.3, 9
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top