Hide Entire Rows Range
 

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

Hide Entire Rows Range
 

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

If this post helps click Yes
---------------
Jacob Skaria


"QuickLearner" wrote:

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

Hide Entire Rows Range
 

QuickLearner;488913 Wrote:
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 rows
and if any one of them doesn't just contain the word "Minimum" then that
entire row will be hidden. This means that *every *cell in a given row
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 one
cell, *anywhere *in the row, to be "Minimum" for the entire row to be
hidden.
I don't think you want that either.
Are you meaning to search only a specific column for the word
"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 cells
is the word "Minimum" then the if statement in your macro could read:
If cl.Value = "Minimum" Then MyRange.EntireRow.Hidden
= TrueBut I don't think you want that.


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=134804

Hide Entire Rows Range
 

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

"QuickLearner" wrote:

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

Hide Entire Rows Range
 

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.

"Patrick Molloy" wrote:

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

"QuickLearner" wrote:

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

Hide Entire Rows Range
 

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

If E230 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" wrote in message
...
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.

"Patrick Molloy" wrote:

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

"QuickLearner" wrote:

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

Hide Entire Rows Range
 

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

"QuickLearner" wrote:

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

If E230 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" wrote in message
...
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.

"Patrick Molloy" wrote:

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

"QuickLearner" wrote:

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

Hide Entire Rows Range
 

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

--
Rick (MVP - Excel)


"QuickLearner" wrote in message
...
After looking at the suggestion I have changed the sheet a bit.
now I need to look a solution for.

If E230 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" wrote in
message ...
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.

"Patrick Molloy" wrote:

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

"QuickLearner" wrote:

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