Hide rows in a range if cell is blank (has formulas, but no value)

sdoppke

Well-known Member
Joined
Jun 10, 2010
Messages
647
Well the title pretty much said it all. I am looking have a macro that hides the rows in a column range. Not based on the entire column, but say if there are cells within A25:A50 that are empty (all will have formulas) then hide entire row of those empty cells.

I wont be able to use autofilter, because there is no consitency in the cloumns or rows.

Thanks a ton in advance.

sd
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
I wont be able to use autofilter, because there is no consitency in the cloumns or rows.

Thanks a ton in advance.
sd

Why does that mean you can't use autofilter?
You face the same problem in VBA.
VBA also needs a way to know which column/rows to check.

So...
How do we know which column/rows to check?
 
Upvote 0
Why does that mean you can't use autofilter?
You face the same problem in VBA.
VBA also needs a way to know which column/rows to check.

So...
How do we know which column/rows to check?


Im sorry I didnt think that through before wrote that. I was thinking custom sort.
 
Upvote 0
Well the title pretty much said it all. I am looking have a macro that hides the rows in a column range. Not based on the entire column, but say if there are cells within A25:A50 that are empty (all will have formulas) then hide entire row of those empty cells.

Thanks a ton in advance.

sd


Hi everyone, would anyone be able to point me in the right direction here?

Thanks a ton. :)
 
Upvote 0
Hi everyone, would anyone be able to point me in the right direction here?

Thanks a ton. :)


Ive tried this based on help i recieved on another thread,but it does not work any ideas?

Code:
Dim cell As Range
    Dim myLastRow As Long
    myLastRow = Cells(Rows.Count, "CK").End(xlUp).row
    For Each cell In Range("A5:A" & myLastRow)
        If (cell.Value = "") And (cell.Formulas <> "") Then
            cell.EntireRow.Hidden = True
        End If
    Next

Trying to hide rows that are blank AND have formula

Any ideas?

sd
 
Upvote 0
Could I ask you to have a look at this thread http://www.mrexcel.com/forum/showthread.php?t=553953 particularly its title and Post#5?

If this code snippet finds the first cell of the type you want, then it's no problem to loop through the range of interest and hide any entire rows containing such cells.


Thanks a ton for the reply, im not sure what to do. Feelin dumb, any suggestions?

sd
 
Upvote 0
Ive tried this,but still thinks there is a value in the cell and ignores the hide?

Code:
Sub test()
Application.ScreenUpdating = False
Dim cell As Range
    For Each cell In Range("A23:A32")
        If (cell.Formula <> 0) And (cell.Value = Empty) Then
            cell.EntireRow.Hidden = True
        End If
    Next
Application.ScreenUpdating = True
End Sub

If i take out the value part and just leave the formula part then it will hide.

Any ideas where i am going wrong?

sd
 
Upvote 0
The code below would solve the problem:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
Rich (BB code):
Option Explicit
Hide blank row ()
' akinrotimi, 01/09/2011<o:p></o:p>
http://www.mrexcel.com/forum/showthread.php?t=575765
Application.ScreenUpdating = False
Columns("a:a").Select
    Selection.Copy
    Columns("z:z").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.TextToColumns Destination:=Range("z1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(0, 1), TrailingMinusNumbers:=True
    Range("z1").Select
    ActiveCell.Formula = "h"
    Selection.Copy
    Range("z2:z24").Select
    ActiveSheet.Paste
[z:z].SpecialCells(xlCellTypeBlanks).EntireRow.Select
Selection.EntireRow.Hidden = True
[z1:z1000].Select
Selection.ClearContents
Range("a1").Select
Application.ScreenUpdating = True
end
Cheers!

Rotimi
 
Upvote 0
Hi sd,

Could you try this macro?

Just run it normally and see if it colors yellow the correct rows you want hidden. If so then just replace the part in red by ".hidden=true" (without the quotes)
Rich (BB code):
Sub hide()
Dim lr As Long, e As Range
lr = Range("A" & Rows.Count).End(xlUp).Row
On Error Resume Next
For Each e In Range("A1:A" & lr).SpecialCells(xlFormulas)
 If Len(e) = 0 Then Rows(e.Row).Interior.Color = vbYellow
Next
On Error GoTo 0
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,220
Members
448,554
Latest member
Gleisner2

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