#1
|
|||
|
|||
How to hide rows that have no data but has formula in cells?
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. |
#2
|
|||
|
|||
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 Code:
Sub UnHideRows() Sheets("AutoFill").Rows.EntireRow.Hidden = False End Sub |
#3
|
|||
|
|||
Getting the following error:
--------------------------- Microsoft Visual Basic for Applications --------------------------- Subscript out of range --------------------------- OK Help --------------------------- |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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 Rows: A4-A503 It throws that error. |
#6
|
|||
|
|||
Guess you attached the wrong file,
|
#7
|
|||
|
|||
What I attached earlier was the sample file for testing.
I am using the wrong sheet name here. Will check. |
#8
|
|||
|
|||
Thanks a lot. It was my fault to write wrong sheet name in the code. It's working fine now.
|
#9
|
|||
|
|||
Glad you figured out your error.
You might want to check things out a little further though. Quote:
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 |