CODEWITHSUNDEEP
vbaexcel
ASH
ASH

Reputation: 20322

How to loop through cells and loop through an array, then hide rows based on matches?

I am trying to look at all used cells in ColumnC. If a specific cell in ColumnC contains a string, like 'Row 2' to 'Row 15', then hide the row. Here is the code that I cam up with, but it doesn't do what I expected it to do.

Sub Hide_Rows()
Dim cell As Range
Dim Mainfram(13) As String

    Mainfram(0) = "Day 2"
    Mainfram(1) = "Day 3"
    Mainfram(2) = "Day 4"
    Mainfram(3) = "Day 5"
    Mainfram(4) = "Day 6"
    Mainfram(5) = "Day 7"
    Mainfram(6) = "Day 8"
    Mainfram(7) = "Day 9"
    Mainfram(8) = "Day 10"
    Mainfram(9) = "Day 11"
    Mainfram(10) = "Day 12"
    Mainfram(11) = "Day 13"
    Mainfram(12) = "Day 14"
    Mainfram(13) = "Day 15"

    lrow = Cells(Rows.Count, 1).End(xlUp).Row

    For i = 2 To 15
    For Each cell In Range("C1:C" & lrow)
        pos = InStr(1, cell.Value, Mainfram(i))
        If pos > 1 Then
            cell.EntireRow.Hidden = True
        End If
    Next
    Next i
End Sub

Upvotes: 1

Views: 101

Answers (1)

Vityata
Vityata

Reputation: 43585

This should work:

Option Explicit

Sub Hide_Rows()

    Dim cell            As Range
    Dim Mainfram(13)    As String
    Dim lRow            As Long
    Dim i               As Long
    Dim pos             As Long

    Mainfram(0) = "Day 2"
    Mainfram(1) = "Day 3"
    Mainfram(2) = "Day 4"
    Mainfram(3) = "Day 5"
    Mainfram(4) = "Day 6"
    Mainfram(5) = "Day 7"
    Mainfram(6) = "Day 8"
    Mainfram(7) = "Day 9"
    Mainfram(8) = "Day 10"
    Mainfram(9) = "Day 11"
    Mainfram(10) = "Day 12"
    Mainfram(11) = "Day 13"
    Mainfram(12) = "Day 14"
    Mainfram(13) = "Day 15"

    'you are with C
    lRow = Cells(Rows.Count, 3).End(xlUp).Row

    For i = LBound(Mainfram) To UBound(Mainfram)
        For Each cell In Range("C1:C" & lRow)
            pos = InStr(1, cell.Value, Mainfram(i))
            Debug.Print pos

            If pos >= 1 Then
                cell.EntireRow.Hidden = True
            End If

        Next
    Next i

End Sub

A few mistakes in your code:

  • If pos >= 1 Then is what you need.
  • If you go to i = 15 and you only have declared up to Mainfram(13) you get a nasty error for going outside of array's boundaries.
  • Furthermore, LBound and Ubound is a good way to loop around an array.
  • Last but not least, you need to find probably the last value in C, not in A.

Cheers!

Upvotes: 2

Related Questions