Is it possible to hide all of a row based on a condition if there is no
data in column b and c for that row it should be hidden, or if there is
data in either of these columns then that row should be unhidden?

This would need to be applied to certain sections only of the worksheet
not the whole sheet.

Any ideas on how to do this or if it can be done?

Thanks for your help

Carl


--
mr_teacher
------------------------------------------------------------------------
mr_teacher's Profile: http://www.excelforum.com/member.php...o&userid=34352
View this thread: http://www.excelforum.com/showthread...hreadid=567408

Rows(10).Hidden = Cells(10,"B").Value ="" AND Cells(10,"C").Value = ""

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"mr_teacher" wrote
in message ...

Is it possible to hide all of a row based on a condition if there is no
data in column b and c for that row it should be hidden, or if there is
data in either of these columns then that row should be unhidden?

This would need to be applied to certain sections only of the worksheet
not the whole sheet.

Any ideas on how to do this or if it can be done?

Thanks for your help

Carl


--
mr_teacher
------------------------------------------------------------------------
mr_teacher's Profile:
http://www.excelforum.com/member.php...o&userid=34352
View this thread: http://www.excelforum.com/showthread...hreadid=567408

Hi Bob,

Thanks for the reply. I am guessing this is a macro code that I need to
put in somewhere? I am still pretty much a beginner with these (pretty
much at the stage where I can record them and have a look at them that
way) so if you could tell me how to put it into my workbook that would
be great.

Bob Phillips Wrote:
Rows(10).Hidden = Cells(10,"B").Value ="" AND Cells(10,"C").Value = ""



Is the 10 in this referring to a number of rows?

Being more specific, I am looking to apply this rule to rows 7 to 42,
and then rows 166 to 196
Thanks a lot for your help

Carl


--
mr_teacher
------------------------------------------------------------------------
mr_teacher's Profile: http://www.excelforum.com/member.php...o&userid=34352
View this thread: http://www.excelforum.com/showthread...hreadid=567408

Try this macro:
Sub spechide(certsect As Range)
Dim teljesrng As Range, szurtrng As Range, kozos As Range
Range("A1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:="="
Selection.AutoFilter Field:=3, Criteria1:="="
Set teljesrng = ActiveSheet.AutoFilter.Range.Columns(1)
Set teljesrng = teljesrng.Offset(1, 0).Resize(teljesrng.Rows.Count - 1)
On Error Resume Next
Set szurtrng = teljesrng.SpecialCells(xlVisible)
Selection.AutoFilter
Set kozos = Intersect(szurtrng, certsect)
kozos.Select
kozos.EntireRow.Hidden = True
On Error GoTo 0
End Sub

Use like this:
Sub test()
Dim certsect As Range
Set certsect = Rows("7:42") 'change next time to "166:196"
Call spechide(certsect)
End Sub


Regards,
Stefi

€žmr_teacher€� ezt Ã*rta:


Hi Bob,

Thanks for the reply. I am guessing this is a macro code that I need to
put in somewhere? I am still pretty much a beginner with these (pretty
much at the stage where I can record them and have a look at them that
way) so if you could tell me how to put it into my workbook that would
be great.

Bob Phillips Wrote:
Rows(10).Hidden = Cells(10,"B").Value ="" AND Cells(10,"C").Value = ""



Is the 10 in this referring to a number of rows?

Being more specific, I am looking to apply this rule to rows 7 to 42,
and then rows 166 to 196
Thanks a lot for your help

Carl


--
mr_teacher
------------------------------------------------------------------------
mr_teacher's Profile: http://www.excelforum.com/member.php...o&userid=34352
View this thread: http://www.excelforum.com/showthread...hreadid=567408

Hi Bob,

Based on your solution I tried to find a simple solution to mr_teacher's
problem, see below, but it doesn't work, because certsect.Row gives a
constant value of 7 (the first row of certsect). Isn't there a trick that
replaces certsect.Row by the current row numbers of each row of certsect
respectively?

Sub test()
Dim As Range
Set certsect = Rows("7:42")
certsect.EntireRow.Hidden = Cells(certsect.Row, "B").Value = "" And
Cells(certsect.Row, "C").Value = ""
End Sub

Regards,
Stefi


€žBob Phillips€� ezt Ã*rta:


Rows(10).Hidden = Cells(10,"B").Value ="" AND Cells(10,"C").Value = ""

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"mr_teacher" wrote
in message ...

Is it possible to hide all of a row based on a condition if there is no
data in column b and c for that row it should be hidden, or if there is
data in either of these columns then that row should be unhidden?

This would need to be applied to certain sections only of the worksheet
not the whole sheet.

Any ideas on how to do this or if it can be done?

Thanks for your help

Carl


--
mr_teacher
------------------------------------------------------------------------
mr_teacher's Profile:
http://www.excelforum.com/member.php...o&userid=34352
View this thread: http://www.excelforum.com/showthread...hreadid=567408

Before I do that, do you want to explain exactly what you want to do, so
that I can tailor it.

The code that I gave just hides or unhides row 10 based on columns B and C.
Do you want to just run it on the activerow, or a range of rows? What are
the rules? Also, do you want to run it on demand, or as those cells are
changed?

BTW, once it gets hidden, how do you add data in B and C that would cause it
to be unhidden?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"mr_teacher" wrote
in message ...

Hi Bob,

Thanks for the reply. I am guessing this is a macro code that I need to
put in somewhere? I am still pretty much a beginner with these (pretty
much at the stage where I can record them and have a look at them that
way) so if you could tell me how to put it into my workbook that would
be great.

Bob Phillips Wrote:
Rows(10).Hidden = Cells(10,"B").Value ="" AND Cells(10,"C").Value = ""



Is the 10 in this referring to a number of rows?

Being more specific, I am looking to apply this rule to rows 7 to 42,
and then rows 166 to 196
Thanks a lot for your help

Carl


--
mr_teacher
------------------------------------------------------------------------
mr_teacher's Profile:
http://www.excelforum.com/member.php...o&userid=34352
View this thread: http://www.excelforum.com/showthread...hreadid=567408

The data in the table is found from a VLOOKUP from another worksheet.
There can be a maximum of 35 entries in the table and minimum of zero.
I would ideally like it where all rows within the table are hidden if
Columns B and C both contain the text "zzz" (apologies I did put blank
before but I had forgotten I had applied conditional formatting to hide
text when it was "zzz") for that particular row and that this would
change on demand based upon results from the table wher it is looking
up from.

This would firstly be applied to data that is in rows 7 to 42 and then
secondly to data that is in rows 166 to 196. I would like the formula
to be looking at these specific ranges but to be able to change to
hidden / unhidden depending upon data being in columns b and c for
them.

Hope that makes a bit more sense? Thanks for all the help!

Carl

Bob Phillips Wrote:
Before I do that, do you want to explain exactly what you want to do,
so
that I can tailor it.

The code that I gave just hides or unhides row 10 based on columns B
and C.
Do you want to just run it on the activerow, or a range of rows? What
are
the rules? Also, do you want to run it on demand, or as those cells
are
changed?

BTW, once it gets hidden, how do you add data in B and C that would
cause it
to be unhidden?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"mr_teacher"
wrote
in message
...

Hi Bob,

Thanks for the reply. I am guessing this is a macro code that I need
to
put in somewhere? I am still pretty much a beginner with these
(pretty
much at the stage where I can record them and have a look at them
that
way) so if you could tell me how to put it into my workbook that
would
be great.

Bob Phillips Wrote:
Rows(10).Hidden = Cells(10,"B").Value ="" AND Cells(10,"C").Value =
""



Is the 10 in this referring to a number of rows?

Being more specific, I am looking to apply this rule to rows 7 to
42,
and then rows 166 to 196
Thanks a lot for your help

Carl


--
mr_teacher

------------------------------------------------------------------------
mr_teacher's Profile:
http://www.excelforum.com/member.php...o&userid=34352
View this thread:
http://www.excelforum.com/showthread...hreadid=567408



--
mr_teacher
------------------------------------------------------------------------
mr_teacher's Profile: http://www.excelforum.com/member.php...o&userid=34352
View this thread: http://www.excelforum.com/showthread...hreadid=567408

I think you would have to loop it Stefi

Sub test()
Dim certsect As Range
Dim certrow As Range

Set certsect = Rows("7:42")
For Each certrow In certsect.Rows
certrow.Hidden = Cells(certrow.row, "B").Value = "" And _
Cells(certrow.row, "C").Value = ""
Next row
End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Stefi" wrote in message
...
Hi Bob,

Based on your solution I tried to find a simple solution to mr_teacher's
problem, see below, but it doesn't work, because certsect.Row gives a
constant value of 7 (the first row of certsect). Isn't there a trick that
replaces certsect.Row by the current row numbers of each row of certsect
respectively?

Sub test()
Dim As Range
Set certsect = Rows("7:42")
certsect.EntireRow.Hidden = Cells(certsect.Row, "B").Value = "" And
Cells(certsect.Row, "C").Value = ""
End Sub

Regards,
Stefi


"Bob Phillips" ezt írta:


Rows(10).Hidden = Cells(10,"B").Value ="" AND Cells(10,"C").Value =
""

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"mr_teacher"
wrote
in message
...

Is it possible to hide all of a row based on a condition if there is
no
data in column b and c for that row it should be hidden, or if there
is
data in either of these columns then that row should be unhidden?

This would need to be applied to certain sections only of the
worksheet
not the whole sheet.

Any ideas on how to do this or if it can be done?

Thanks for your help

Carl


--
mr_teacher

------------------------------------------------------------------------
mr_teacher's Profile:
http://www.excelforum.com/member.php...o&userid=34352
View this thread:
http://www.excelforum.com/showthread...hreadid=567408

This should do it

Sub Test()
Dim i As Long

For i = 7 To 42
Rows(i).Hidden = Cells(i, "B").Value = "zzz" And Cells(i, "C").Value
= "zzz"
End If

For i = 166 To 196
Rows(i).Hidden = Cells(i, "B").Value = "zzz" And Cells(i, "C").Value
= "zzz"
End If

End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"mr_teacher" wrote
in message ...

The data in the table is found from a VLOOKUP from another worksheet.
There can be a maximum of 35 entries in the table and minimum of zero.
I would ideally like it where all rows within the table are hidden if
Columns B and C both contain the text "zzz" (apologies I did put blank
before but I had forgotten I had applied conditional formatting to hide
text when it was "zzz") for that particular row and that this would
change on demand based upon results from the table wher it is looking
up from.

This would firstly be applied to data that is in rows 7 to 42 and then
secondly to data that is in rows 166 to 196. I would like the formula
to be looking at these specific ranges but to be able to change to
hidden / unhidden depending upon data being in columns b and c for
them.

Hope that makes a bit more sense? Thanks for all the help!

Carl

Bob Phillips Wrote:
Before I do that, do you want to explain exactly what you want to do,
so
that I can tailor it.

The code that I gave just hides or unhides row 10 based on columns B
and C.
Do you want to just run it on the activerow, or a range of rows? What
are
the rules? Also, do you want to run it on demand, or as those cells
are
changed?

BTW, once it gets hidden, how do you add data in B and C that would
cause it
to be unhidden?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"mr_teacher"
wrote
in message
...

Hi Bob,

Thanks for the reply. I am guessing this is a macro code that I need
to
put in somewhere? I am still pretty much a beginner with these
(pretty
much at the stage where I can record them and have a look at them
that
way) so if you could tell me how to put it into my workbook that
would
be great.

Bob Phillips Wrote:
Rows(10).Hidden = Cells(10,"B").Value ="" AND Cells(10,"C").Value =
""



Is the 10 in this referring to a number of rows?

Being more specific, I am looking to apply this rule to rows 7 to
42,
and then rows 166 to 196
Thanks a lot for your help

Carl


--
mr_teacher

------------------------------------------------------------------------
mr_teacher's Profile:
http://www.excelforum.com/member.php...o&userid=34352
View this thread:
http://www.excelforum.com/showthread...hreadid=567408



--
mr_teacher
------------------------------------------------------------------------
mr_teacher's Profile:
http://www.excelforum.com/member.php...o&userid=34352
View this thread: http://www.excelforum.com/showthread...hreadid=567408

Hi Bob,

That looks like it will do the trick perfectly!

Where should I place that code to execute it? Should I use the tab to
view code for the sheet or is it elsewhere? Gradually learning about
these things as I go along! The help on here is really helping though!

Thanks a lot

Carl
Bob Phillips Wrote:
This should do it

Sub Test()
Dim i As Long

For i = 7 To 42
Rows(i).Hidden = Cells(i, "B").Value = "zzz" And Cells(i, "C").Value
= "zzz"
End If

For i = 166 To 196
Rows(i).Hidden = Cells(i, "B").Value = "zzz" And Cells(i, "C").Value
= "zzz"
End If

End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"mr_teacher"
wrote
in message
...

The data in the table is found from a VLOOKUP from another
worksheet.
There can be a maximum of 35 entries in the table and minimum of
zero.
I would ideally like it where all rows within the table are hidden
if
Columns B and C both contain the text "zzz" (apologies I did put
blank
before but I had forgotten I had applied conditional formatting to
hide
text when it was "zzz") for that particular row and that this would
change on demand based upon results from the table wher it is
looking
up from.

This would firstly be applied to data that is in rows 7 to 42 and
then
secondly to data that is in rows 166 to 196. I would like the
formula
to be looking at these specific ranges but to be able to change to
hidden / unhidden depending upon data being in columns b and c for
them.

Hope that makes a bit more sense? Thanks for all the help!

Carl

Bob Phillips Wrote:
Before I do that, do you want to explain exactly what you want to
do,
so
that I can tailor it.

The code that I gave just hides or unhides row 10 based on columns
B
and C.
Do you want to just run it on the activerow, or a range of rows?
What
are
the rules? Also, do you want to run it on demand, or as those
cells
are
changed?

BTW, once it gets hidden, how do you add data in B and C that
would
cause it
to be unhidden?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"mr_teacher"

wrote
in message
...

Hi Bob,

Thanks for the reply. I am guessing this is a macro code that I
need
to
put in somewhere? I am still pretty much a beginner with these
(pretty
much at the stage where I can record them and have a look at
them
that
way) so if you could tell me how to put it into my workbook that
would
be great.

Bob Phillips Wrote:
Rows(10).Hidden = Cells(10,"B").Value ="" AND
Cells(10,"C").Value =
""



Is the 10 in this referring to a number of rows?

Being more specific, I am looking to apply this rule to rows 7
to
42,
and then rows 166 to 196
Thanks a lot for your help

Carl


--
mr_teacher


------------------------------------------------------------------------
mr_teacher's Profile:
http://www.excelforum.com/member.php...o&userid=34352
View this thread:
http://www.excelforum.com/showthread...hreadid=567408



--
mr_teacher

------------------------------------------------------------------------
mr_teacher's Profile:
http://www.excelforum.com/member.php...o&userid=34352
View this thread:
http://www.excelforum.com/showthread...hreadid=567408



--
mr_teacher
------------------------------------------------------------------------
mr_teacher's Profile: http://www.excelforum.com/member.php...o&userid=34352
View this thread: http://www.excelforum.com/showthread...hreadid=567408

No just put it in a standard code model (Alt-F11, InsertModule) and then
call it from Excel (ToolsMacroMacros...< select Test and Run).

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"mr_teacher" wrote
in message ...

Hi Bob,

That looks like it will do the trick perfectly!

Where should I place that code to execute it? Should I use the tab to
view code for the sheet or is it elsewhere? Gradually learning about
these things as I go along! The help on here is really helping though!

Thanks a lot

Carl
Bob Phillips Wrote:
This should do it

Sub Test()
Dim i As Long

For i = 7 To 42
Rows(i).Hidden = Cells(i, "B").Value = "zzz" And Cells(i, "C").Value
= "zzz"
End If

For i = 166 To 196
Rows(i).Hidden = Cells(i, "B").Value = "zzz" And Cells(i, "C").Value
= "zzz"
End If

End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"mr_teacher"
wrote
in message
...

The data in the table is found from a VLOOKUP from another
worksheet.
There can be a maximum of 35 entries in the table and minimum of
zero.
I would ideally like it where all rows within the table are hidden
if
Columns B and C both contain the text "zzz" (apologies I did put
blank
before but I had forgotten I had applied conditional formatting to
hide
text when it was "zzz") for that particular row and that this would
change on demand based upon results from the table wher it is
looking
up from.

This would firstly be applied to data that is in rows 7 to 42 and
then
secondly to data that is in rows 166 to 196. I would like the
formula
to be looking at these specific ranges but to be able to change to
hidden / unhidden depending upon data being in columns b and c for
them.

Hope that makes a bit more sense? Thanks for all the help!

Carl

Bob Phillips Wrote:
Before I do that, do you want to explain exactly what you want to
do,
so
that I can tailor it.

The code that I gave just hides or unhides row 10 based on columns
B
and C.
Do you want to just run it on the activerow, or a range of rows?
What
are
the rules? Also, do you want to run it on demand, or as those
cells
are
changed?

BTW, once it gets hidden, how do you add data in B and C that
would
cause it
to be unhidden?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"mr_teacher"

wrote
in message
...

Hi Bob,

Thanks for the reply. I am guessing this is a macro code that I
need
to
put in somewhere? I am still pretty much a beginner with these
(pretty
much at the stage where I can record them and have a look at
them
that
way) so if you could tell me how to put it into my workbook that
would
be great.

Bob Phillips Wrote:
Rows(10).Hidden = Cells(10,"B").Value ="" AND
Cells(10,"C").Value =
""



Is the 10 in this referring to a number of rows?

Being more specific, I am looking to apply this rule to rows 7
to
42,
and then rows 166 to 196
Thanks a lot for your help

Carl


--
mr_teacher


------------------------------------------------------------------------
mr_teacher's Profile:
http://www.excelforum.com/member.php...o&userid=34352
View this thread:
http://www.excelforum.com/showthread...hreadid=567408



--
mr_teacher

------------------------------------------------------------------------
mr_teacher's Profile:
http://www.excelforum.com/member.php...o&userid=34352
View this thread:
http://www.excelforum.com/showthread...hreadid=567408



--
mr_teacher
------------------------------------------------------------------------
mr_teacher's Profile:
http://www.excelforum.com/member.php...o&userid=34352
View this thread: http://www.excelforum.com/showthread...hreadid=567408