Hide Entire Rows Range

[QuickLearner]

Hi At the moment I have this code to hide entire row
and it is working fine.


Sub HideRows()
Dim MyRange As Range, cl As Range

Set MyRange = Sheet3.Range("A23:IV25")
Application.ScreenUpdating = False
For Each cl In MyRange
If cl.Value <> "Minimum" Then cl.EntireRow.Hidden = True
Next cl
Application.ScreenUpdating = True
End Sub


Now another question is
How can I hide the Entire Range("A23:IV25") if the Value is "Minimum"?

Thanks

 

[Jacob Skaria]

Range("A23:IV25").EntireRow.Hidden = True

If this post helps click Yes

 

[p45cal]

Hi At the moment I have this code to hide entire row
and it is working fine.


Sub HideRows()
Dim MyRange As Range, cl As Range

Set MyRange = Sheet3.Range("A23:IV25")
Application.ScreenUpdating = False
For Each cl In MyRange
If cl.Value <> "Minimum" Then cl.EntireRow.Hidden = True
Next cl
Application.ScreenUpdating = True
End Sub


Now another question is
How can I hide the Entire Range("A23:IV25") if the Value is "Minimum"?

Thanks

You realise this runs through *all* of the 768 cells on the three row
and if any one of them doesn't just contain the word "Minimum" then tha
entire row will be hidden. This means that *every *cell in a given ro
must contain "Minimum" for it not to be hidden!
I don't think you want that.
Now if you change the '<>' n the code to '=' then it will only take on
cell, *anywhere *in the row, to be "Minimum" for the entire row to b
hidden.
I don't think you want that either.
Are you meaning to search only a specific column for the wor
"Minimum", then hide the entire row based on that?

If you want to hide the entire range in one go if any one of the cell
is the word "Minimum" then the if statement in your macro could read:
If cl.Value = "Minimum" Then MyRange.EntireRow.Hidde
= TrueBut I don't think you want that

 

[Patrick Molloy]

checking every cell in a row is OTT for this. Every developer will have his
own method

I show the same using FIND in my code, but you could use the COUNTIF()
function too
if the count is zero, hide the row.


Sub hideRows()
Dim rw As Long
Dim c As Long
On Error Resume Next
For rw = 23 To 25
'If Range(rw).Find("minumum") Is Nothing Then
c = WorksheetFunction.CountIf(Rows(rw), "minimum")
If c = 0 Then
If Err.Number <> 0 Then
Rows(rw).Hidden = True
Err.Clear
End If
End If
Next
End Sub

 

[Patrick Molloy]

the code says hide if any cell contains the word.
You can easily alter this to that if any cell doesn't contain the word that
the row gets hidded...but I agree with p45cal that that seems odd.

 

[QuickLearner]

After looking at the suggestion I have changed the sheet a bit.
now I need to look a solution for.

If E23>0 then SHOW row 23,24,25 and should already be Hidden when the sheet
open.

What would be the best way to do it?

Thanks

 

[Patrick Molloy]

something like
instead of
If Err.Number <> 0 Then
Rows(rw).Hidden = True
Err.Clear
End If

xxx If Err.Number <> 0 Then
Rows(rw).Hidden = (Err.Number <> 0)
Err.Clear
xxx End If

this returns TRUE or FALSE rather than just the TRUE

 

[Rick Rothstein]

This will do what you asked...

If Range("E23").Value > 0 Then Rows("23:25").Hidden = True

but I have to ask... is there any possibility that E23 can change to 0 (or
even a negative number?) and, if it does, you will want your code to unhide
those rows? If so, you can use this line of code instead...

Rows("23:25").Hidden = Range("E23").Value > 0