Macro to hide rows with null values or blanks in all columns.

R420

New Member
Joined
Mar 24, 2022
Messages
3
Hi,

I have a sheet in my workbook that compiles info from 4 other sheets using formulas to call up cells from other sheets, for example: ='STEAM (SH)'!D3 . this leaves me with rows that have info in every column or just one column at times.
I want to be able to quickly hide the rows that have null values or blanks in all columns but keep the row if it has any data showing.
I tried following tutorials that used identifier columns but since my cells have formulas, it wouldn't differentiate between a cell with null data in it and a cell that is actually blank.

is there a macro that I could use that would hide/unhide all rows that are returning null values (blanks) as well as actual blank cells with no data in them in all columns but keep the rows that might have only one cell returning data. My range I am working in is A3:M100.

Thanks for your time.
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
I can update this if you need to check a range of columns instead of an entire row, but if this works, then great!
VBA Code:
Sub HideUnhide()

Dim rng As Range
Set rng = Range("A3:M3")

For i = 1 To 98
    If Application.CountA(rng.Cells(i, 1).EntireRow) = 0 Then
        If rng.Cells(i, 1).EntireRow.Hidden Then
            rng.Cells(i, 1).EntireRow.Hidden = False
        Else
            rng.Cells(i, 1).EntireRow.Hidden = True
        End If
    End If
Next

End Sub
 
Upvote 0
I would suggest using helper column to create autofilter on this column
VBA Code:
Option Explicit
Sub test()
Application.ScreenUpdating = False
Dim i&, count&, rng As Range, cell As Range, helper As Range
Set rng = Range("A2:M2")
ActiveSheet.AutoFilterMode = False
Set helper = Range("N3:N100") ' or using any column that available
helper.ClearContents
    For i = 1 To (100 - 2) ' if working range was A2:M100, loop through each row
        count = 0
        For Each cell In rng.Offset(i, 0)
            count = count + Len(cell) ' sum all length of cells each row
        Next
        With helper.Cells(i)
            If count = 0 Then ' all values are blank or null: sum of length = 0
                .Value = "" ' marks row that all values are blank or null, with blank
            Else: .Value = "@" ' marks used row , with "@"
            End If
        End With
    Next
Range("N2:N100").AutoFilter ' adjust range, to include helper column
Application.ScreenUpdating = True
End Sub
Book1
ABCDEFGHIJKLMN
1
2
31@
4111@
5 
61@
7aaa @
8 
9
10
11
12
13
14
15
16
17
18Formulla in E7, E8: =""
Sheet1
Cell Formulas
RangeFormula
E5,E7:E8E5=""
 
Upvote 0
I can update this if you need to check a range of columns instead of an entire row, but if this works, then great!
VBA Code:
Sub HideUnhide()

Dim rng As Range
Set rng = Range("A3:M3")

For i = 1 To 98
    If Application.CountA(rng.Cells(i, 1).EntireRow) = 0 Then
        If rng.Cells(i, 1).EntireRow.Hidden Then
            rng.Cells(i, 1).EntireRow.Hidden = False
        Else
            rng.Cells(i, 1).EntireRow.Hidden = True
        End If
    End If
Next

End Sub

Thanks for the reply.

I'm pretty new to macro's and am not an Excel expert so forgive me if I'm missing something. I copied this into the visual basic window and ran the macro, nothing appeared to happen. I've attached an image of the top portion of my spreadsheet as an example. I've added data to random cells while I test this but I am trying to automatically hide all the yellow (empty) rows and since this changes I'd prefer something automated. All of these cells have formulas in them like the one shown in the image to read info from cells on other sheets.
 

Attachments

  • tos.PNG
    tos.PNG
    47.9 KB · Views: 21
Upvote 0
I would suggest using helper column to create autofilter on this column
VBA Code:
Option Explicit
Sub test()
Application.ScreenUpdating = False
Dim i&, count&, rng As Range, cell As Range, helper As Range
Set rng = Range("A2:M2")
ActiveSheet.AutoFilterMode = False
Set helper = Range("N3:N100") ' or using any column that available
helper.ClearContents
    For i = 1 To (100 - 2) ' if working range was A2:M100, loop through each row
        count = 0
        For Each cell In rng.Offset(i, 0)
            count = count + Len(cell) ' sum all length of cells each row
        Next
        With helper.Cells(i)
            If count = 0 Then ' all values are blank or null: sum of length = 0
                .Value = "" ' marks row that all values are blank or null, with blank
            Else: .Value = "@" ' marks used row , with "@"
            End If
        End With
    Next
Range("N2:N100").AutoFilter ' adjust range, to include helper column
Application.ScreenUpdating = True
End Sub
Book1
ABCDEFGHIJKLMN
1
2
31@
4111@
5 
61@
7aaa @
8 
9
10
11
12
13
14
15
16
17
18Formulla in E7, E8: =""
Sheet1
Cell Formulas
RangeFormula
E5,E7:E8E5=""

Thanks for the reply.

I tried this macro as well and it did mark my N column with @ symbols but it did not differentiate between empty rows and rows with data. It placed @ symbols on every row.

I am very new to using macro's in Excel so if there are things you are assuming I know, then I assure you I do not haha
 

Attachments

  • tos 2.PNG
    tos 2.PNG
    53.8 KB · Views: 14
Upvote 0

Forum statistics

Threads
1,224,820
Messages
6,181,155
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top