Excel Macro to delete specific rows

[bthumble]

Hello Everyone, I have a macro code that is supposed to delete the rows on two separate tabs based on the date that is input in a pop up text box, The problem is that the date is a formula and the macro will only delete the rows if I convert the date from a formula to a value. What modifications are needed in the code so it will work if the date is a formula?

Sub DeleteRows1()
  Dim ym As Variant
  
  ym = InputBox("Enter year-month, ex: 2023-5")
  If ym = "" Then Exit Sub
  
  If Mid(ym, 5, 1) <> "-" Then
    MsgBox "Enter year-month, ex: 2023-5", vbCritical
    Exit Sub
  End If
  If Not IsNumeric(Left(ym, 4)) Then
    MsgBox "The year is not correct", vbCritical
    Exit Sub
  End If
  If Not IsNumeric(Split(ym, "-")(1)) Then
    MsgBox "The month is not correct", vbCritical
    Exit Sub
  End If
  If Not IsDate("1/" & Split(ym, "-")(1) & "/" & Split(ym, "-")(1)) Then
    MsgBox "The date is not correct", vbCritical
    Exit Sub
  End If
  
  With Sheets("Data1").Range("BC:BC")
    .Replace ym, "#N/A", xlWhole
    On Error Resume Next
      .SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
    On Error GoTo 0
  End With
  With Sheets("Data2").Range("AE:AE")
    .Replace ym, "#N/A", xlWhole
    On Error Resume Next
      .SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
    On Error GoTo 0
  End With
  
'MsgBox "Done"
  
End Sub

 

[Fuji]

It fully depends on your formula.
If the formula used ROW() function, it deletes the row(s), but those dates might be revived after re-calculation.

Anyway...

Sub DeleteRows1()
    Dim ym As Variant, a(1), e, i As Long
    ym = InputBox("Enter year-month, ex: 2023-5")
    If ym = "" Then Exit Sub
    If (Not ym Like "2###-[1-9]") * (Not ym Like "2###-1[0-2]") Then
      MsgBox "Enter year-month, ex: 2023-5", vbCritical
      Exit Sub
    End If
    ym = Split(ym, "-")
    For Each e In Array("Data1", "Data2")
        With Sheets(e)
            With Intersect(.UsedRange, .Columns("bc"))
                a(0) = .Formula: a(1) = .Value
                For i = 1 To UBound(a(0), 1)
                    If IsDate(a(1)(i, 1)) Then
                        If (Year(a(1)(i, 1)) = Val(ym(0))) * (Month(a(1)(i, 1)) = Val(ym(1))) Then
                            a(0)(i, 1) = "#N/A"
                        End If
                    End If
                Next
                .Formula = a(0)
                On Error Resume Next
                .SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
                On Error GoTo 0
            End With
        End With
    Next
    'MsgBox "Done"
End Sub

 

[bthumble]

Thank you for the response. Tried the code and it did the same as the original code that I created. Also your code did not reference any specific rows to check the date. Does this code automatically look for the date? Thanks.

 

[Joe4]

Thank you for the response. Tried the code and it did the same as the original code that I created. Also your code did not reference any specific rows to check the date. Does this code automatically look for the date? Thanks.

Please post the formula you are using to calculate the date.
My guess is that is actually create a text value, and not a valid date value.
But we may be able to convert it to a valid date with a slight tweak to your formula, which should allow your code to then work.
But we need to see exactly what your formula looks like!

 

[Akuini]

What is the cell format in col BC & AE? Is it "yyyy-m"?

 

[bthumble]

The original date in the data is formatted as date in column E (shown as 5/27/2024). Based on this date, I have a formula "=YEAR(E2)&"-"&MONTH(E2)" to calculate the Year-Month and is displayed as 2024-5. This is the date that I want the macro to look at and delete the specified rows.

 

[Akuini]

The original date in the data is formatted as date in column E (shown as 5/27/2024). Based on this date, I have a formula "=YEAR(E2)&"-"&MONTH(E2)" to calculate the Year-Month and is displayed as 2024-5. This is the date that I want the macro to look at and delete the specified rows.

Try this:

Sub bthumble_1()
  Dim ym As Variant
  Dim n As Long
  ym = InputBox("Enter year-month, ex: 2023-5")
  If Not ym Like "####-#" Then
    MsgBox "Enter year-month, ex: 2023-5", vbCritical
    Exit Sub
  End If

Application.ScreenUpdating = False
  With Sheets("Data1").Range("BC:BC")
    n = .Find(What:="*", LookIn:=xlValues, lookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, SearchFormat:=False).Row
    For i = n To 2 Step -1
        If .Cells(i).Text = ym Then .Cells(i).EntireRow.Delete
    Next
  End With
  
  With Sheets("Data2").Range("AE:AE")
    n = .Find(What:="*", LookIn:=xlValues, lookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, SearchFormat:=False).Row
    For i = n To 2 Step -1
        If .Cells(i).Text = ym Then .Cells(i).EntireRow.Delete
    Next
  End With
Application.ScreenUpdating = True
End Sub

 

[bthumble]

Thank you for the response. Tried this code and it causes Excel to lock up on my computer. It has been non responsive for over ten minutes now.

 

[Akuini]

About how many rows is your dataset?

 

[bthumble]

There are approximately 45,000 rows on the two tabs.