CODEWITHSUNDEEP
excelvba
accurist
accurist

Reputation: 51

Loop through the range VBA

Looking for a simple loop through the range (say column A range("A5:A15")) if there is a blank cell within that range I need the entire row/rows associated with the blank cell/cells to be hidden.

I was thinking of something like this to accommodate various ranges but get "type Mismatch" error. Any reasons why

Sub test()

    Dim rng As Range, cell As Variant, ar As Variant
    Dim Rng1 As Range, Rng2 As Range, Rng3 As Range, Rng4 As Range

    Dim MyArray(1 To 4) As Range

      With ThisWorkbook.Worksheets("sheet1")

      'Set MyArray = rng


       Set MyArray(1) = Range("O8:O17")
       Set MyArray(2) = Range("O55:O64")
       Set MyArray(3) = Range("G37:G46")
       Set MyArray(4) = Range("G89:G98")


        'ar = Array(Rng1, Rng2, Rng3, Rng4)

        'Set rng = .Range("O8:O17")

        For Each cell In MyArray

            If Len(cell.Value) < 1 Then

               cell.EntireRow.Hidden = True

            End If

        Next cell

    End With

End Sub

?

Upvotes: 1

Views: 2709

Answers (4)

Tim Stack
Tim Stack

Reputation: 3248

Try the following

Option Explicit
Sub youcouldhaveatleasttriedtodosomethingyourself()

Dim r1 As Range, r2 As Range, c As Range, target As Range

With Workbooks(REF).Sheets(REF)
    Set r1 = .Range("A1:A54")
    Set r2 = .Range("F3:F32")

    Set target = Application.Union(r1, r2) 
    For Each area In target.Areas
        For Each c In area
            If c.Value = vbNullString Then .Rows(c.Row).EntireRow.Hidden = True
        Next c
    Next area
End With

End Sub

Please note that I now have set two exemplifying ranges. You can always add more range variables to the Union function.

Upvotes: 0

Mikku
Mikku

Reputation: 6654

Something Like this:

You can put it in a subject:

For Each cell In Range("A5:A15")

    If Len(cell.Value) < 1 Then

        cell.EntireRow.Hidden = True

    End If

Next
For Each cell In Range("A40:A55")

    If Len(cell.Value) < 1 Then

        cell.EntireRow.Hidden = True

    End If

Next

New Answer :

Dim rng As Range, cell As Variant, ar As Variant
Dim Rng1 As Range, Rng2 As Range, Rng3 As Range, Rng4 As Range

Dim MyArray(1 To 4) As Range

  With ThisWorkbook.Worksheets("sheet1")

  'Set MyArray = rng


   Set MyArray(1) = Range("O8:O17")
   Set MyArray(2) = Range("O55:O64")
   Set MyArray(3) = Range("G37:G46")
   Set MyArray(4) = Range("G89:G98")


    'ar = Array(Rng1, Rng2, Rng3, Rng4)

    'Set rng = .Range("O8:O17")
Dim i As Integer

    For i = LBound(MyArray) To UBound(MyArray)

            For Each cell In MyArray(i)

             If Len(cell.Value) < 1 Then

               cell.EntireRow.Hidden = True

            End If

        Next

    Next

End With

Upvotes: 2

Jeff S.
Jeff S.

Reputation: 26

This takes full advantage of the Excel VBA model. I'm guessing it's faster than the above but have not conducted performance tests.

Dim Cell As Range
For Each Cell In Range("A5:A15").SpecialCells(xlCellTypeBlanks)
   Cell.EntireRow.Hidden = True
Next

Upvotes: 0

Error 1004
Error 1004

Reputation: 8220

Try:

Option Explicit

Sub test()

    Dim rng As Range, cell As Range

    With ThisWorkbook.Worksheets("Sheet1")

        Set rng = .Range("A5:A15")

        For Each cell In rng

            If cell.Value = "" Then

                .Rows(cell.Row).EntireRow.Hidden = True

            End If

        Next cell

    End With

End Sub

Upvotes: 0

Related Questions