Hide Empty Rows with formula from Range

[adam]

Hi anyone,

I'm having the following code which removes empty rows from an excel sheet.

Code: Select all

Sub RemoveEmptyRows()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Dim rw As Long, iCol As Long
    For rw = ActiveSheet.UsedRange.Rows.Count To 1 Step -1
      If Application.CountA(Rows(rw).EntireRow) = 0 Then _
            Rows(rw).Delete
    Next
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
  End Sub

I need help to modify the code so that when the macro button is clicked empty rows from the range A16:A35 are hidden.

I want to point out that I have applied formulas to columns C, F, H & J within this range (A16:A35).
I don't want the formulas to be deleted when the rows are hidden.

I would be happy if you could help me with this.

Thanks in advance

[HansV]

If the rows contain formulas, they are not empty.
What should we look at to decide whether a row is "empty"?

[adam]

ok can it be modified to hide the rows if column A is emptyif the column A is empty

[HansV]

Try this:

Code: Select all

Sub HideEmptyRows()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Dim rw As Long
    For rw = 16 To 35
        Range("A" & rw).EntireRow.Hidden = (Range("A" & rw) = "")
    Next rw
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

[adam]

Thanks for the help Hans. Your code works the way as I have requested

[adam]

By the way how may I modify the above code If I want to unhide all the hidden rows within the range

[HansV]

See the thread Unhide Hidden Rows. It gives you all the information you need.

[adam]

Thanks Hans for the recommendation. I did replace the line

Range("A" & rw).EntireRow.Hidden = (Range("A" & rw) = "")

with
Range("A" & rw).EntireRow.Hidden = False

and I guess it works

[adam]

The following worsheet event code hides the rows mentioned in the code when the text mentioned in the code is written in the cell mentioned in the code. How could this code be changed so that it hides all the rows below the text when the text in the code is written in any row of the column "A" of the worksheet.

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then Rows(10:1000).Hidden = (Target.Value = "End of Report")
End Sub

[HansV]

Try

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim oCell As Range
  If Not Intersect(Range("A:A"), Target) Is Nothing Then
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Range("A:A").EntireRow.Hidden = False
    Set oCell = Range("A:A").Find(What:="End of Report", _
      LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlNext)
    If Not oCell Is Nothing Then
      Range(oCell.Offset(1, 0), Range("A" & Rows.Count)).EntireRow.Hidden = True
    End If
    Application.ScreenUpdating = True
    Application.EnableEvents = True
  End If
End Sub

[adam]

Thanks for the help. The code was doing exactly I had wanted it to be. I do really appreciate the help provided.