Hiding Rows

  • Thread starter Thread starter mr_teacher
  • Start date Start date
M

mr_teacher

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
 
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)
 
Hi Bob,

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

Bob said:
Rows(10).Hidden = Cells(10,"B").Value ="" AND Cells(10,"C").Value = ""
Click to expand...

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

Car
 
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,

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)
Click to expand...
 
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)
 
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
 
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)
 
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)
 
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 t
view code for the sheet or is it elsewhere? Gradually learning abou
these things as I go along! The help on here is really helping though!

Thanks a lot

Carl
Bob said:
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" <[email protected]
wrote
in messag
Click to expand...
 
No just put it in a standard code model (Alt-F11, Insert>Module) and then
call it from Excel (Tools>Macro>Macros...< select Test and Run).

--
HTH

Bob Phillips

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