Hide rows with blank cells

[Poptarticus]

Hi all,
I'm looking for a macro for Excel 2003 that will detect all rows with non-blank cells and hide those rows.

I have about 3-4 different columns that contain some blank cells and I want to only select this data.

Any help gang?

Thanks!

Pops

 

[machopicho]

Try:

Sub HideRows() 
 
 Application.ScreenUpdating = False 
 
 Dim LR As Long 
 Dim n As Long 
 
 LR = Selection.Rows.Count 
 
 With Selection 
 For n = LR To 1 Step -1 
 If Application.CountA(.Rows(n)) = 0 Then _ 
 .Cells(n, 1).EntireRow.Hidden = True 
 Next n 
 End With 
 
 Application.ScreenUpdating = True 
 
 End Sub

 

[Poptarticus]

Thanks, but that didn't seem to do anything. All rows with a blank cell in them (yet containing other data) didn't hide. I'm wondering if that was written to hide completely blank rows?

Thanks for the attempt though!

 

[machopicho]

Excuse my mistake:

Sub HideRows()
 
 Application.ScreenUpdating = False
 
 Dim LR As Long
 Dim n As Long
 
 LR = ActiveSheet.UsedRange.Rows.Count
 
 With Selection
 For n = LR To 1 Step -1
 If Application.CountA(.Rows(n)) = 0 Then _
 .Cells(n, 1).EntireRow.Hidden = True
 Next n
 End With
 
 Application.ScreenUpdating = True
 
 End Sub

Enjoy!!

 

[Akinrotimi]

Hi Poptarticus,

I set up the solution below with the assuption that the columns you want to test are columns A to D.This means that if a row has data in any of columns A-D the macro would hide the entire row.

Option Explicit
Sub Hide Rows with blank cells()
' akinrotimi, 13/08/2011
http://www.mrexcel.com/forum/newreply.php?do=newreply&noquote=1&p=2826730
Application.ScreenUpdating = False
    Range("E2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(OR(RC[-4]="""",RC[-3]="""",RC[-2]="""",RC[-1]=""""),""Q"","""")"
    Range("E2").Select
    Selection.AutoFill Destination:=Range("E2:E10000"), Type:=xlFillDefault
    Range("E2:E10000").Select
    Range("E2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(OR(RC[-4]="""",RC[-3]="""",RC[-2]="""",RC[-1]=""""),""Q"","""")"
    Range("E2").Select
    Selection.AutoFill Destination:=Range("E2:E10000")
    Range("E2:E10000").Select
    Range("E2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(AND(RC[-4]="""",RC[-3]="""",RC[-2]="""",RC[-1]=""""),""Q"","""")"
    Range("E2").Select
    Selection.AutoFill Destination:=Range("E2:E10000")
    Range("E2:E10000").Select
    Range("E4").Select
     Columns("E:E").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("E8").Select
    Application.CutCopyMode = False
    Columns("E:E").Select
    Selection.TextToColumns Destination:=Range("E1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(0, 1), TrailingMinusNumbers:=True
    Range("E1").Select
    Columns("E:E").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
    Columns("E:E").Select
    Selection.ClearContents
    Range("E1").Select
    Application.ScreenUpdating = True

Regards

Rotimi