Excel VBA: Hide Rows with FormulaR1C1

Question

I am trying to hide the rows that contain the dates of holidays. In another module, I have the command "yes" in cell R3 to activate this sub so I just need to figure out the sub. There are no errors, it just doesn't hide the rows I need it to hide. The formula I'm using works to find the holidays because it properly highlights the cells when I put it into Conditional Formatting. Code is below:

Sub HideHolidays()

Dim beginRow As Long, endRow As Long, chkCol As Long, chkCommCol As Long, rowCnt As Long

Application.ScreenUpdating = False
beginRow = 4
HolidaybeginRow = 2
endRow = ActiveWorkbook.Sheets("2017 All Districts").Cells(Rows.Count, 1).End(xlUp).Row '<--| set 'endRow' to column A last not empty cell row index
chkCol = 1
chkCommCol = 17
chkHolCol = 18
'Set Sheets("2017 All Districts").Range(beginRow, chkCol).FormulaR1C1 = "=Match($A1,Holidays!$B$2:$B$11,0)"

'Rows.EntireRow.Hidden = False 'unhides all rows. Subsequent code will hide relevant ones

If Cells(3, chkHolCol).Value = "Yes" Then '<--| if Q3 value is "Yes"
    For rowCnt = beginRow To endRow '<--| loop through the "limit" rows indexes
        With Cells(rowCnt, chkCol) '<--| reference current cell to be cheked
            If ActiveCell.FormulaR1C1 = "=Match($A1,Holidays!$B$2:$B$11,0)" Then
                Application.EntireRow.Hidden
            End If
        End With
    Next
End If
Application.ScreenUpdating = True

End Sub

HIDE WEEKEND CODE: Sub HideWeekends() Dim beginRow As Long, endRow As Long, chkCol As Long, chkCommCol As Long, rowCnt As Long

Application.ScreenUpdating = False
beginRow = 4
endRow = Cells(Rows.Count, 1).End(xlUp).Row '<--| set 'endRow' to column A     last not empty cell row index
chkCol = 1
chkCommCol = 17

Rows.EntireRow.Hidden = False 'unhides all rows. Subsequent code will hide relevant ones

If Cells(3, chkCommCol).Value = "Yes" Then '<--| if Q3 value is "Yes"
   For rowCnt = beginRow To endRow '<--| loop through the "limit" rows indexes
    With Cells(rowCnt, chkCol) '<--| reference current cell to be cheked
        .EntireRow.Hidden = Weekday(.Value2, vbMonday) > 5 '<--| set its corresponding Row 'Hidden' property to True if currently referenced cell has wanted color
    End With
Next
End If
Application.ScreenUpdating = True
End Sub

Answer 1

some issues:

• you use ActiveCell.FormulaR1C1 but

  • ActiveCell is neither "updated" (with some Select statement)

  • nor has it any relation with the actual loop variable which is Cells(rowCnt, chkCol)

• you use FormulaR1C1 while "=Match($A1,Holidays!$B$2:$B$11,0)" is not in a R1C1 style

• with ws.Cells(rowCnt, chkCol).FormulaR1C1 = "=Match($A1,Holidays!$B$2:$B$11,0)" you are comparing two Formula expression, while

  • the actual content of your checked cells is a real date (not a formula returning the date)

  • you want to check if the current cell content (a date) is in a given range: that you can achieve by means of Find() method of Range object or Match() method of Application object

• you're not qualifying all ranges up to their worksheet object

for all what above you may try this refactoring of your code:

edited to follow OP's request to reverse the code if "2017 All Districts" sheet "R3" cell content is "Yes"

Sub HideHolidays()

    Dim beginRow As Long, rowCnt As Long, chkCol As Long

    beginRow = 4
    chkCol = 1
    With Worksheets("2017 All Districts") '<--| reference your sheet
        With .Range(.Cells(beginRow, chkCol), .Cells(.Rows.Count, chkCol).End(xlUp)) '<--| reference referenced sheet 'chkCol' column from row 'beginRow' down to last not empty one
            .EntireRow.Hidden = False 'unhides all referenced range rows: subsequent code will hide relevant ones if referenced sheet R3 cell isn't "Yes"
            If .Parent.Range("R3").Value <> "Yes" Then Exit Sub    '<--| exit if referenced range sheet R3 cell value isn't "Yes"
            For rowCnt = 1 To .Rows.Count '<--| loop through all referenced range cells
                .Cells(rowCnt, 1).EntireRow.Hidden = Not IsError(Application.Match(CDbl(.Cells(rowCnt, 1)), Worksheets("Holidays").Range("$B$2:$B$11"), 0)) '<--| hide current cell entire row if its content doesn't match any value in "Holidays" sheet range $B$2:$B$11
            Next
        End With
    End With

End Sub

Answer 2

You should fully qualify the objects you want to work with.

Sub HideHolidays()

Dim beginRow As Long, endRow As Long, chkCol As Long, chkCommCol As Long, rowCnt As Long
Dim ws As Worksheet
Application.ScreenUpdating = False
Set ws = ActiveWorkbook.Sheets("2017 All Districts")
beginRow = 4
HolidaybeginRow = 2
endRow = ws.Cells(Rows.Count, 1).End(xlUp).Row '<--| set 'endRow' to column A last not empty cell row index
chkCol = 1
chkCommCol = 17
chkHolCol = 18
'Set Sheets("2017 All Districts").Range(beginRow, chkCol).FormulaR1C1 = "=Match($A1,Holidays!$B$2:$B$11,0)"

'Rows.EntireRow.Hidden = False 'unhides all rows. Subsequent code will hide relevant ones

If ws.Cells(3, chkHolCol).Value = "Yes" Then '<--| if Q3 value is "Yes"
    For rowCnt = beginRow To endRow '<--| loop through the "limit" rows indexes
        With ws.Cells(rowCnt, chkCol) '<--| reference current cell to be cheked
            If ws.Cells(rowCnt, chkCol).FormulaR1C1 = "=Match($A1,Holidays!$B$2:$B$11,0)" Then
                ws.Cells(rowCnt, chkCol).EntireRow.Hidden 'use the range object to hide and not application.
            End If
        End With
    Next
End If
Application.ScreenUpdating = True

End Sub

Answer 3

I suggest that you write Option Explicit at the top of your code sheets so that you will be forced to declare all your variables. Actually, you had a lot of constants, and the most efficient way to declare numeric constants is by means of an enumeration. This is what I did.

Option Explicit

    Private Enum Num
        FirstHolidayRow = 2
        ChkRow               ' no number declared means previous + 1
        FirstRow
        chkCol = 1
        chkCommCol = 17
        ChkHolCol
    End Enum

Note that enums must be placed at the top of code sheets, before any procedures. Next I revised your code. Now it looks like this:-

Sub HideHolidays()
    ' 24 Mar 2017

    Dim WsAllDistricts As Worksheet
    Dim LastRow As Long
    Dim R As Long

    Set WsAllDistricts = ActiveWorkbook.Sheets("2017 All Districts")

    Application.ScreenUpdating = False
    With WsAllDistricts
        '<--| set 'endRow' to column A last not empty cell row index
        LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    End With

    With WsAllDistricts
        If .Cells(ChkRow, ChkHolCol).Value = "Yes" Then '<--| if Q3 value is "Yes"
            For R = FirstRow To LastRow '<--| loop through the "limit" rows indexes
                If InStr(1, .Cells(R, chkCol).Formula, _
                            "Holidays!$B$2:$B$11,0)", vbTextCompare) Then
                    .Rows(R).EntireRow.Hidden = True
                End If
            Next R
        End If
    End With

    Application.ScreenUpdating = True
End Sub

It works, but I'm not sure that it does what you want it to do. The presumption is that there is only one sheet. If there is another it doesn't make its appearance in your code. Please bear in mind that you must specify the workbook to find a sheet, a sheet to find a row, and a column to find a cell. Sometimes these specifications are implicit, like a sheet knowing perfectly well to which workbook it belongs, but that isn't the case with Range("A1") which could be on any sheet. And, of course, if you wish to address the ActiveCell you must first activate a cell. All the addressing discrepancies were resolved.

There is a problem with your desire to look for the formula because the formula has the syntax =MATCH($A9 in it, where row 9 is variable. So, there is only one formula exactly like this in your sheet. I resolved this by looking only for the match range "Holidays!$B$2:$B$11,0)". If this isn't enough there are other ways of dealing with this issue with more precision.