How to hide rows that have no data but has formula in cells?

LearnerExcel · #1 02-12-2018, 06:21 AM

How to hide rows that have no data but has formula in cells? I am retrieving data from C:\TEST\TempData.xls to C:\TEST\AutoFill.xlsm. I want to hide the rows which have no data but has only formula in the Cells. Empty data rows are vary time to time. Sometimes I require around 200 hundred records and sometimes it exceeds 400 records. That's why there are lot of blank rows. See the attached files for reference.

NoSparks · #2 02-12-2018, 05:10 PM

If any cell within your data area is blank, this will hide that row.

Code:

Sub HideRowsMissingData()

    Dim cl As Range, i As Integer
        
Application.ScreenUpdating = False
With Sheets("AutoFill")
    For Each cl In .Range("B6:B500")
        If cl.Value = "" Then
            .Rows(cl.Row).Hidden = True
        Else
            For i = 1 To 4
                If cl.Offset(, i).Value = "" Then
                    .Rows(cl.Row).Hidden = True
                    Exit For
                End If
            Next i
        End If
    Next cl
End With
Application.ScreenUpdating = True
End Sub

and this will un-hide all hidden rows

Code:

Sub UnHideRows()
Sheets("AutoFill").Rows.EntireRow.Hidden = False
End Sub

LearnerExcel · #3 02-12-2018, 08:36 PM

Getting the following error:
---------------------------
Microsoft Visual Basic for Applications
---------------------------
Subscript out of range
---------------------------
OK Help
---------------------------

NoSparks · #4 02-12-2018, 09:56 PM

The macros work fine for me in the AutoFill.xlsm file you attached to this thread.
If they didn't I would not have posted them.

LearnerExcel · #5 02-12-2018, 11:53 PM

Yes it works quite fine with that file. But now I am using the same code on other Excel file changing the ranges.

Code:

For Each cl In .Range("A4:A503")

Code:

For i = 1 To 13

Columns: A to M
Rows: A4-A503

It throws that error.

NoSparks · #6 02-13-2018, 12:14 AM

Guess you attached the wrong file,

LearnerExcel · #7 02-13-2018, 02:50 AM

What I attached earlier was the sample file for testing.

I am using the wrong sheet name here. Will check.

LearnerExcel · #8 02-13-2018, 03:41 AM

Thanks a lot. It was my fault to write wrong sheet name in the code. It's working fine now.

NoSparks · #9 02-13-2018, 06:31 AM

Glad you figured out your error.

You might want to check things out a little further though.

Quote:

Columns: A to M
Rows: A4-A503

cl.Offset(,13) will be column N

PS: please update your profile to reflect the version of Excel you use, thanks.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Split data in cells and duplicate rows	ballpoint	Excel Programming	6	02-07-2018 05:52 PM
eliminating blan rows between cells in a column cantaining data	FUGMAN	Excel Programming	6	03-01-2017 07:35 AM
Formula to hide '0' from blank referenced cells	formuladummy	Excel	3	05-08-2014 02:33 AM
Hide Rows	YounesB3	Excel	2	10-29-2012 05:40 AM
Sum Formula in the range with Numeric and NonNumeric data cells	Spanec	Excel	2	01-12-2012 09:15 AM