Hide rows if all cells return no values

[kapi98]

Hi there,

Could you please advice how I could hide rows that don't return any value?
I've tried to adjust two different approaches but I guess they didn't work as there are still formulas in cells.

Dim row As Range
Dim sheet As Worksheet
Set sheet = ActiveSheet

For i = 1 To sheet.UsedRange.Rows.Count

    Set row = sheet.Rows(i)
    If WorksheetFunction.CountA(row) = 0 Then
        row.EntireRow.Hidden = True
    End If

Next i

Dim neValues As Range, MyRange As Range

Set MyRange = Rows("2:40")

On Error Resume Next
Set neValues = Intersect(ActiveCell.EntireRow.SpecialCells(xlConstants), MyRange)
On Error GoTo 0

If neValues Is Nothing  Then
    row.EntireRow.Hidden = True
End If

Thanks

 

[Michael M]

What is in the row that doesn't return a value, numbers, text, formula...if formula what is it

 

[kapi98]

It's a formula that displays another sheet's values under certain conditions.

 

[Michael M]

Giving us the formula would have helped, but try

Sub MM1()
Dim row As Range, r As Long, sh As Worksheet
Set sh = ActiveSheet
For r = 1 To sh.UsedRange.Rows.Count
    If Range("A" & r).Value = "" Then 'change the "" to 0 if thats what is returned
      Rows(r).Hidden = True
   End If
Next r
End Sub

 

[kapi98]

=IF(AND('SHEET1'!$M7<=A$2,'SHHET1'!$M7>44445,A$2<='SHEET1'!$Q7),'SHEET1'!$D7&" "&RIGHT('SHEET1'!$C7,3),"")

Thanks for your try. Unfortunately, it doesn't work well as the value is not always return in Column A.

 

[Trebor76]

Try this:

Option Explicit
Sub Macro1()

    Dim lngMyRow As Long, lngLastRow As Long, lngLastCol As Long
    Dim strLastCol As String
    Dim ws As Worksheet

    Set ws = ThisWorkbook.ActiveSheet
    
    If WorksheetFunction.CountA(ws.Cells) > 0 Then
        Application.ScreenUpdating = False
        lngLastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        lngLastCol = ws.Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
        strLastCol = Split(ws.Cells(lngLastRow, lngLastCol).Address, "$")(1)
        For lngMyRow = lngLastRow To 1 Step -1
            If Evaluate("SUMPRODUCT(('" & ws.Name & "'!A" & lngMyRow & ":" & strLastCol & lngMyRow & "="""")*('" & ws.Name & "'!A" & lngMyRow & ":" & strLastCol & lngMyRow & "=""""))") = lngLastCol Then
                ws.Rows(lngMyRow).EntireRow.Hidden = True
            End If
        Next lngMyRow
        Application.ScreenUpdating = True
    End If

End Sub

Regards,

Robert

 

[kapi98]

Awesome - thanks for your help

 

[Trebor76]

Thanks for letting us know and you're welcome