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!
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

NeonRedSharpie

Well-known Member
Joined
Jul 14, 2014
Messages
1,678
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
 

RosieRose

Board Regular
Joined
Jul 29, 2014
Messages
75
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.
 

NeonRedSharpie

Well-known Member
Joined
Jul 14, 2014
Messages
1,678
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?
 

RosieRose

Board Regular
Joined
Jul 29, 2014
Messages
75

ADVERTISEMENT

yes, this is what I want it to do. However, when I ran the VBA. it hid every row. nothing was left visible
 

RosieRose

Board Regular
Joined
Jul 29, 2014
Messages
75
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
 

Beyond_avarice

Board Regular
Joined
Nov 13, 2012
Messages
195
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

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)
 

schoe1da

New Member
Joined
Sep 29, 2014
Messages
1
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
 

RosieRose

Board Regular
Joined
Jul 29, 2014
Messages
75
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.
 

NeonRedSharpie

Well-known Member
Joined
Jul 14, 2014
Messages
1,678
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,502
Messages
5,523,296
Members
409,508
Latest member
Afc

This Week's Hot Topics

Top