OzGrid

How to use VBA code to hide row based on month

Requirement:

The user is trying to hide row that are less than current month of last year. 

Sub dHide()


Dim i As Long
Dim lr As Long
Dim answer As Date

answer = DateAdd("m", -12, Date)

lr = Cells(Rows.Count, 1).End(xlUp).Row
    With Range("A3:Z3" & lr)

For i = lr To 3 Step -1

    If Month(Range("J" & i).Value) < Month(answer) Then
    'If Year(Range("J" & i).Value) < Year(answer) Then

      Range("J" & i).EntireRow.Hidden = True

   'End If

    End If

Next i

End With

On Error Resume Next

Application.ScreenUpdating = True


End Sub

https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/1193128-hide-row-based-on-month

Solution:

Sub dHide()
    Application.ScreenUpdating = False
    Dim i As Long
    Dim lr As Long
    Dim answer As Date
    answer = DateAdd("m", -12, Date)
    lr = Range("J" & Rows.Count).End(xlUp).Row
    For i = lr To 3 Step -1
        If Month(Range("J" & i).Value) < Month(answer) Then
            Range("J" & i).EntireRow.Hidden = True
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

Dates less than one year ago is what your datediff does. I read your goal as one year to the last day of the month before current month. If that was not your intent, the equality check can be easily changed. I did have to change a few of your column J values to test the criterion.

In any case, for these things, one does not have to hide by bottom up but it doesn't hurt. I like to do it all at once. e.g.

Sub Main()
  Dim rJ As Range, c As Range, rH As Range
  Set rJ = Range("J3", Cells(Rows.Count, "J").End(xlUp))
  For Each c In rJ
    If IsDate(c) And c > 0 And _
      c.Value <= DateSerial(Year(Date) - 1, Month(Date), 1) - 1 Then
      If rH Is Nothing Then
        Set rH = c
        Else: Set rH = Union(rH, c)
      End If
    End If
  Next c
  If Not rH Is Nothing Then rH.EntireRow.Hidden = True
End Sub

Obtained from the OzGrid Help Forum.

Solution provided by Mumps and Kenneth Hobson.

See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets

See also:

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.

Gallery