CODEWITHSUNDEEP
excelvba
KC0904
KC0904

Reputation: 13

VBA to hide row once date is entered

I have a growing spreadsheet and I want to hide rows once specific cells in the same column have a date entered. I have tried the below 2 vbas and could not get them to work the way I want them to. The column where the date is to be entered is H or 8 and the column is title as "Milestone Finished Date".

Code #1:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 8 And Target.Value <> "" Then
        Target.EntireRow.Hidden = True
    End If
End Sub

The problem with Code #1 is that a user can hit enter and the row is hidden.I only want the row to be hidden if the cell contains a date (mm/dd/yyyy).

Code #2

Option Explicit

Private lastSelectedCell As Range

Private Sub Worksheet_Activate()
    Set lastSelectedCell = Range("A1")
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ' If the most recently selected cell was in column 8 ('H')
    If (lastSelectedCell.Column = 8) Then
        ' If the previously selected cell was a date on or before today
        If ((lastSelectedCell.Value <= Date) And (lastSelectedCell.Value > 0)) Then
            ' Hide the entire row
            Rows(lastSelectedCell.Row).EntireRow.Hidden = True
        End If
    End If
    Set lastSelectedCell = Target
End Sub

With Code #2 I keep getting a Run-Time error "91" Object Variable or with Block Variable Not Set. I have not able to figure this one out.

Any help would be greatly appreciated.

Upvotes: 1

Views: 107

Answers (1)

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96753

I like your Code #1 with a tiny change:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 8 And IsDate(Target.Value) Then
        Target.EntireRow.Hidden = True
    End If
End Sub

I like IsDate() because it can tell that 1/22/2018 is a Date and 43122 is not.

Upvotes: 2

Related Questions