Hiding rows question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there any way to hide rows in a sheet if a certain cell is blank? For example, if cell A30 is blank, is there a way I can hide the rows that correspond to it? These include: A77, A114, A151, 188, A225, and A262. Any help would be greatly appeciated. Thanks!
 
Hi John
try the following (searches column A for row 1 to 37 - as looking at
your example each 37th roe should be hidden / is related):

Sub hide_rows()
Dim RowNdx As Long
Dim hide_index as integer

For RowNdx = 37 To 1 Step -1
If Cells(RowNdx, "A").Value = "" Then
for hide_index = 1 to 7
Rows(RowNdx*hide_index).hidden = True
next hide_index
End If
Next RowNdx
End Sub
 
Hi. Thanks for responding to my question. I was wondering is the macro you gave me would work with my sheet and I just want to explain it further to you. I have a list of names in A7-A40. A7 is linked to A54, A92, A130, A168, A206, and A244. A8 is linked to A55, A93, A131, A169, A207, A245. You get the idea (I hope my examples are clear). What I am wondering is if A7 is blank, is there a macro or function that will hide the rows of A54, A92, A130, A168, A206, and A244? I need to see if there is something that can help me hide the rows that correspond if any of the cells (A7-A40) are blank. Thanks so much for any help

John
 
Hi John
so there is the following algorithm behind the corresponding rows:
A7 -> A54: add 47 rows
A54 -> A92: add 38 rows
A92 -> A130 add 38 rows
... add 38 rows

And you only want to hide the corresponding rows. Try
Sub hide_rows()
Dim RowNdx As Long
Dim hide_index as integer

For RowNdx = 40 To 7 Step -1
If Cells(RowNdx, "A").Value = "" Then
for hide_index = 0 to 5
Rows(RowNdx+47 +hide_index*38).hidden = True
next hide_index
End If
Next RowNdx
End Sub


If you also want to hie the row in A1:A40 add the following line in the
IF clause:
Rows(RowNdx).hidden = True
 
That worked great. Thanks so much. One last question. The
corresponding rows hide when they are blank, but if I add
someone later on, is there a way to have the rows unhide
themselves if something is put in them?
 
O.K. try
Sub toggle_hide_rows()
Dim RowNdx As Long
Dim hide_index as integer

For RowNdx = 40 To 7 Step -1
If Cells(RowNdx, "A").Value = "" Then
for hide_index = 0 to 5
Rows(RowNdx+47 +hide_index*38).hidden = True
next hide_index
else
for hide_index = 0 to 5
Rows(RowNdx+47 +hide_index*38).hidden = false
next hide_index
End If

Next RowNdx
End Sub
 
Okay I am sorry to bother you again. I forgot to ask this. Okay so I have the names of my students in rows A7-A40 in my "Grade Sheet". When one of these cells is blank, their corresponding cells now hide when I run the macro you sent me (thank you so much!). I forgot to add that these rows (A7-A40 on the "Grade Sheet") are linked to rows A7-A40 on another sheet called "Attendance". Is there a way to have it so that in addition to the code you already sent me, that if any cell in A7-A40 on the "Attendance" sheet is blank, then the corresponding row (A7-A40 on the "Grade Sheet") will be hidden? Thanks again for any help

John
 
Hi John
not quite sure but if you have linked the cells with formulas the macro
should word or do I misunderstood you. Not sure what you mean with
linked!
Maybe you can post some example rows (plain text - no attachments
please) to make this clearer
 
Last question (I swear!). I forgot to ask how to hide rows on my "Attendance" sheet in rows A7-A40 if any of them are blank. Thanks for any help.

John
 
Hi
this should be easy for you, just adapt the macro :-)
Or try the following macro on your attendance sheet

Public Sub DeleteBlankRows()
Dim R As Long
Dim Rng As Range
On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

set rng = worksheets("Attendance sheet").range("7:40")
For R = Rng.Rows.Count To 1 Step -1
If Application.WorksheetFunction.CountA(Rng.Rows(R).EntireRow) = 0
Then
Rng.Rows(R).hidden = true
End If
Next R

EndMacro:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub
 
I'm sorry if this is unclear. I am not really explaning
it very well. I have a list of students that I put in at
the start if the scool year on my "Attendance" sheet. I
put the names in rows A7-A40. And then these cells are
linked to rows A7-A40 on my "Grades" sheet. So is the
name "Bob" is in cell A7 on the "Attendance" sheet, the
the name "Bob" would appear in cell A7 on the "Grades"
sheet. I hope that is clearer. I have two questions about
this:

1) Is there a way to hide rows between A7-A40 on
my "Attendance" sheet if any cells between A7 and A40 are
blank?

2) I was also wondering if there was a way that if any
cell between A7 and A40 on the "Attendance" sheet is
blank, the the corresponding row (A7-A40) on the "Grades"
sheet will be hidden.

Thanks for any help!
 
Hi John
just use the macro i posted in this thread to your other question for
both sheets (you just have to change the worksheet names)
 
Okay I tried this macro and I tried to run it. But it
said "Compile error, syntax error" and this lin e showed
up in red:

If Application.WorksheetFunction.CountA(Rng.Rows
(R).EntireRow) = 0
Then

and this line showed up in yellow:

Public Sub DeleteBlankRows()

Did I do something wrong?
I am so sorry to be a pain about this. I have absolutely
no clue how this stuff works. Thanks for the help.
 
Back
Top