Loop through the range VBA

Question

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

?

Have you tried anything yet ? If Yes can you paste the code also. — Mikku, Jun 06 '19 at 13:22
Check the New Answer Added Below in (https://stackoverflow.com/a/56478651/5720144) — Mikku, Jun 07 '19 at 08:23
@Mikku you are a star It works like a magic! Never crossed ny mind to apply LBound to UBound — accurist, Jun 07 '19 at 08:28

Mikku · Accepted Answer · 2019-06-07T08:23:10.327

2

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

edited Jun 07 '19 at 08:23

answered Jun 06 '19 at 13:28

Mikku

6,538
3
15
38

Mikku a better apporach would be `If cell = vbNullString` – Damian Jun 06 '19 at 13:37
@Damian [checking the string length is actually faster](https://web.archive.org/web/20140712133312/http://www.jpsoftwaretech.com/len-vs-blank-strings-vs-vbnullstring/), although unlikely to be noticeable – Tim Stack Jun 06 '19 at 13:43
@Damian ... I think there are many ways for this. But yeah always to prefer the faster one. – Mikku Jun 06 '19 at 13:47
@accurist ... Great! Accept the answer if it worked fine. – Mikku Jun 06 '19 at 13:48
Just did. Great help Thank you. Just a quick question. Say I have more that one range to loop through. ("A5:A15 & "A40:A45") would it be possible to join those ranges together in above code ? – accurist Jun 06 '19 at 14:02
Simply you can copy paste the code once more in the same module and change its range for the second part. Now when you will run your code, it will work on both ranges – Mikku Jun 06 '19 at 14:04
@accurist .. Edited the answer :) – Mikku Jun 06 '19 at 14:59
yes I appreciate that but would it be possible to apply many different ranges in above code rather then repeat the code for each range ? – accurist Jun 06 '19 at 15:14
Yes if you can find a pattern that connects each Range. – Mikku Jun 06 '19 at 15:17
@TimStack Do you think it is possible to apply all those different ranges in one code. If so how can I get about this I still get "Type Mismatch" error on the code above – accurist Jun 07 '19 at 08:17
@accurist I've edited my code conform your request. – Tim Stack Jun 07 '19 at 08:40

Tim Stack · Answer 2 · 2019-06-07T08:39:22.337

0

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.

edited Jun 07 '19 at 08:39

answered Jun 06 '19 at 13:26

Tim Stack

3,209
3
18
39

Tim, `For Each` works faster on sheets than `For i` – Damian Jun 06 '19 at 13:38
@Damian yes... [didn't we talk about this?](https://stackoverflow.com/a/56336838/10540017) – Tim Stack Jun 06 '19 at 13:47
I like using these loops. Don't have to use `.Offset` if I am referring to multiple columns. Keeps my code shorter and cleaner. On the other hand, why not use an array in that case? – Tim Stack Jun 06 '19 at 13:51
Because you are working on the sheet, for each criteria you need to hide a row, also you don't need to use Offset either with this loop. Just use `.Cells(Cell.Row, "A")` for example and that's it. – Damian Jun 06 '19 at 13:56
Yes, but `.Cells(i, 1)` is a little bit shorter :D – Tim Stack Jun 06 '19 at 13:58

score 0 · Answer 3 · answered Jun 06 '19 at 13:40

0

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

answered Jun 06 '19 at 13:40

Error 1004

7,877
3
23
46

This added nothing to the pre-existing answers – Tim Stack Jun 06 '19 at 13:50
@TimStack some useful tips for OP is how to use the `With Statement`, the `Set` of the range and the `.` before `Rows`. – Error 1004 Jun 06 '19 at 13:56
So except for the `Set` statement, no new information – Tim Stack Jun 06 '19 at 13:59
@Tim Stack there are a lot of new things to add. The usage of 'for each' is faster than then 'for i'. The set of the range in a variable help you if clear indicate the ranges and make it easier to add more ranges as OP ask. Also there is no reason to calculate last row when you know the range. More over the code should be as short as you can so avoid declare variables without reason ( start point & last point.). Lastly vbNullString and Is empty may cause issues so is better to use "" – Error 1004 Jun 06 '19 at 15:34

score 0 · Answer 4 · answered Jun 06 '19 at 14:03

0

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

answered Jun 06 '19 at 14:03

Jeff S.

26
4

this works as a treat too. what if i had two separate ranges in above scenario (A5:A15) and (A40:A55) how could i concatenate those two ranges together in above code? – accurist Jun 06 '19 at 14:13
@accurist Use `Union`, like so: `For Each Cell In Union(Range("A5:A15"), Range("A40:A55")).SpecialCells(xlCellTypeBlanks)` – Chronocidal Jun 07 '19 at 09:01
Or For Each Cell In Range("A5:A15,A40:A55").SpecialCells(xlCellTypeBlanks) – Jeff S. Jun 07 '19 at 16:20

Loop through the range VBA

4 Answers4

Linked