1

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.

Improve this question
12
  • 1
    Seeing as how your range string is exceeding 255 chars, it would be of use to tell us how you construct this range. If it is done dynamically through code, you might be better off using another method with variables or something such. Commented Aug 21, 2019 at 5:48
  • 1
    Also you can use r.SpecialCells(xlCellTypeBlanks) instead of a check using a if condition if a cell is blank. Commented Aug 21, 2019 at 5:53
  • 1
    You know you can use autofilter to hilde blank rows in Col H? Better than looping :) Commented Aug 21, 2019 at 6:01
  • 1
    BTW if you wish that you have a different answer, feel free to put that and aacept it (i.e if you want it) :) Commented Aug 21, 2019 at 12:08
  • 1
    Well, for one things comments are "volatile" on Stack Overflow: they can be and are often deleted, especially on older posts. In addition, the purpose of the site is a repository of information "for everyone", in a Question & Answer format - so questions and answers need to be in the right place. This isn't a "free help desk" providing support just for you :-) Luckily, Siddharth has helped us all out with a quick and thorough Answer :-) Commented Aug 21, 2019 at 12:14

1 Answer 1

Reset to default
3

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 :)

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.