CODEWITHSUNDEEP
excelvbaloops
CTEAGUE212
CTEAGUE212

Reputation: 45

How to hide all rows which have blank cells in a certain column?

I am trying to hide all rows which have blank cells in a certain column (Column H in the SOW tab).

This same macro (different rows but same syntax) worked on the third tab in this workbook. I checked, each row in the range is correct. Could it be that the range is too long?

Sub HideRowsInSOW_LOOP()
Dim r As Range, c As Range
Set r = Range("H27:H46,H48:H67,H69:H88,H90:H109,H111:H130,H132:H151,H153:H172,H174:H193,H195:H214,H216:H235,H237:H256,H258:H277,H279:H298,H300:H319,H321:H340,H342:H361,H369:H388,H390:H409,H411:H430,H432:H451,H453:H472,H474:H493,H495:H514,H516:H535,H537:H556,H558:H577,H579:H598,H600:H619,H621:H640,H642:H661,H663:H682,H684:H703")
Application.ScreenUpdating = False
For Each c In r
    If c.Value = 0 Then
        c.EntireRow.Hidden = True
    Else
        c.EntireRow.Hidden = False
    End If
Next c
Application.ScreenUpdating = True
End Sub

When running this, I get the following error:

run-time error '1004' error message: Method 'Range' of object '_Global' failed

I have a much longer macro which serves the same purpose successfully and am worried about it slowing down my workbook/keep hearing that loop is better anyway.

Upvotes: 1

Views: 544

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149287

Your code may not be working for the relevant sheet as the range is not fully qualified. Set r = Range("H27:H46,.... The range is referring to the activesheet.

Here is one way using AutoFilter which will hide the blank rows in a column. This is much faster than looping in a huge dataset.

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim lRow As Long
    Dim rngColH As Range
    Dim rngToHide As Range

    '~~> Chage this to the relevant sheet
    Set ws = Sheet1

    With ws
        '~~> Remove any autofilter if any
        .AutoFilterMode = False

        '~~> Find last row in Col H
        lRow = .Range("H" & .Rows.Count).End(xlUp).Row

        '~~> Construct your range
        Set rngColH = .Range("H1:H" & lRow)

        '~~> Filter on blanks
        With rngColH
            .AutoFilter Field:=1, Criteria1:="="
            '~~> These are your blank rows (i.e if there are any)
            Set rngToHide = .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow
        End With

        .AutoFilterMode = False

        '~~> Hide the rows
        If Not rngToHide Is Nothing Then rngToHide.Hidden = True
    End With
End Sub

Here is how it works...

enter image description here

Using .SpecialCells(xlCellTypeBlanks) as @Mikku suggested is also a good approach :)

Upvotes: 3

Related Questions