Thread: Modify VBA code to hide blanks rows

Hi,

I have a great code that I want to modify. The code hides rows if the sum of the row is smaller or larger than zero. But I want to modify the code so it hides the rows only if it is blank. So a row with a string of text in it will not get hidden.

Sub Komprimera()
     
    Dim HiddenRow&, RowRange As Range, RowRangeValue&
     
     '*****************************
     '< Set the 1st & last rows to be hidden >
    Const FirstRow As Long = 4
    Const LastRow As Long = 65
     
     '< Set the columns that may contain data >
    Const FirstCol As String = "C"
    Const LastCol As String = "AZ"
     '*****************************
     
    ActiveWindow.DisplayZeros = False
    Application.ScreenUpdating = False
     
    For HiddenRow = FirstRow To LastRow
         
         '(we're using columns B to G here)
        Set RowRange = Range(FirstCol & HiddenRow & _
        ":" & LastCol & HiddenRow)
         
         'sums the entries in cells in the RowRange
        RowRangeValue = Application.Sum(RowRange.Value)
         
        If RowRangeValue <> 0 Then
             'there's something in this row - don't hide
            Rows(HiddenRow).EntireRow.Hidden = False
        Else
             'there's nothing in this row yet - hide it
            Rows(HiddenRow).EntireRow.Hidden = True
        End If
         
    Next HiddenRow
     
    Application.ScreenUpdating = True
     
End Sub

I am making an excel file for come co-workers and it needs to be as clean and simple as possible. One way to remove unused rows would be to set a macro to a button. Clicking this button would hide all unused rows. A filter would not work as well. I only need to figure out how to change

If RowRangeValue <> 0 Then

to search for word and numbers.

hi, Ranew, I've amended my last post, try to change that line

watersev, a good step forward

The code hides blank rows! Unfortunately I have alot of rows containing formulas, but returns blanks. I need to hide them aswell.

I found this;

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
'Hide rows with formulas but no data
Dim cell As Range
Application.ScreenUpdating = False
With ActiveSheet.UsedRange
.Rows.Hidden = False
For Each cell In .Columns(1).SpecialCells(xlCellTypeFormulas)
If cell.Value = "" Or cell.Value = 0 Then cell.entirerow.Hidden = True
Next cell
End With
End Sub

I don't know if it is of any help...