Hide/Unhide row VBA

S

Scafidel

A form sheet fills a "document" sheet with headings and information with a
blank row between each part. But when a heading repeats (by cell formula),
it appears as empty, so I am using this VBA formula to hide the empty row.
But I have many rows just like it and I would like a single formula that
would check the whole sheet. Luckily, it is every sixth row, i.e.,
A24,A30,A36,A42, etc.

Private Sub Worksheet_Change(ByVal Target As Range)
If Me.Range("A30") = "" Then
Rows("30").Hidden = True
ElseIf Me.Range("A30") > "" Then
Rows("30").Hidden = False
End If

How would I have one formula check every sixth row down to say, A200?
Thanks
 
J

Jarek Kujawa

Private Sub Worksheet_Change(ByVal Target As Range)

For Each cell In Range("H30:H200").Cells
If IsEmpty(cell) And Int(cell.Row / 6) = cell.Row / 6 Then
cell.Rows.EntireRow.Hidden = True
Else
cell.Rows.EntireRow.Hidden = False
End If
Next cell

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top