Code to hide rows

[dpaton05]

I need some code to hide some rows if some cells are blank.

• If C13 is blank, I need to hide row 25
• if C14 is blank I need to hide row 26
• If C15 is blank I need to hide row 27
• If C16 is blank, I need to hide row 28

Can someone help me with the code please?

 

[iliace]

This was one of my earliest VBA questions when I first started using it ~20 years ago The basic idea is something like this:

Public Sub HideRows()
  Dim rng As Excel.Range

  For Each rng In ActiveSheet.Range("C13:16")
    If IsEmpty(rng) Then
      rng.Offset(RowOffset:=25-13).EntireRow.RowHeight = 0
    Else
      rng.Offset(RowOffset:=25-13).EntireRow.AutoFit
    End If
  Next rng
End Sub

 

[dpaton05]

Thanks for the reply.

With that code I get the error message of application defined or object defined error.

 

[dpaton05]

I found some code that only hides the first row. What is it missing so it hides the other rows?

• Dim r As Range
• Application.EnableEvents = False
• For Each r In Me.Range("C13:C16")
• If r.Value = 0 Then
• r.EntireRow.Hidden = True
• Else
• r.EntireRow.Hidden = False
• End If
• Next r
• Application.EnableEvents = True

 

[Michael M]

Maybe this way.....but @iliace has missed a col ref in the range...
Range("C13:16")

Public Sub HideRows()
  Dim cell As Range, n As Long
n = 25
  For Each cell In Range("C13:C16")
    If IsEmpty(cell) Then
      Rows(n).Hidden = True
    End If
    n = n + 1
  Next cell
End Sub

 

[kweaver]

Probably need this line:
For Each rng In ActiveSheet.Range("C13:16")

changed to:

For Each rng In ActiveSheet.Range("C13:C16")

 

[juddaaaa]

Try this one

Sub HideRows()
  Dim rw As Range
  
  For Each rw In ActiveSheet.Rows("13:16")
    If rw.Cells(, "C") = Empty Then
      rw.Offset(12).EntireRow.Hidden = True
    Else
      rw.Offset(12).EntireRow.Hidden = False
    End If
  Next rw
End Sub

 

[dpaton05]

Those code samples both only hide row 25 if C13 is blank. Even if the other rows are blank (C14:C16), rows 26:28 will not get hidden. If there is a value in C13 but not C14,15 or 16, only row 26 gets hidden, not the remaining two that should be hidden.

 

[Michael M]

My code works fine for me !!
Are the cells TRULY blank in the C13:C16 range OR the result of formulas ??

 

[Michael M]

Maybe try it this way as well...

Sub HideRows()
Dim cell As Range, n As Long
n = 25
  For Each cell In Range("C13:C16")
    If cell.Value = "" Then Rows(n).Hidden = True
    n = n + 1
  Next cell
End Sub