Excel VBA to delete specific Year-Month on two separate tabs

[bthumble]

Hello,

Need help with VBA code that will delete a specific Year-Month on two specific sheets based on a prompt for user input.

Sheet1 has Year-Month in column HC and Sheet2 has Year-Month in column BE. Would like the VBA to prompt the user to enter the month-year to delete. For example enter 2023-5 to delete May 2023 rows.

Thanks for your time an deffort.

 

[DanteAmor]

Hi @bthumble:
There are some inconsistencies in your presentation.
It would be easier to understand your request if you accompany it with real examples of your sheet using the XL2BB tool.

For example:

-------------

Sheet1 has Year-Month in column HC

Examples please. Is it a date? or is it a text?
-------------

Sheet2 has Year-Month in column BE.

Examples please. Is it a date? or is it a text?
-------------

Would like the VBA to prompt the user to enter the month-year to delete.

In the sheet you have year-month and you want to capture month-year?
Can you confirm?

-------------

For example enter 2023-5

Here you mention entering year-month but in the previous text you request month-year
Also, can you confirm?

-------------
And finally:

to delete May 2023 rows.

Here is the month with letters and without a hyphen.

-------------
Then review your request and explain with examples, exactly how your data is in the cells (it is very important that you confirm if you have date or text in the cells) and how you are going to capture the data (prompt)

Confirm if you have 05-2023 or 5-2023 in the cell?

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------​

 

[bthumble]

Thanks for the reply.

Attached a screen shot how the field looks like. The cells are actually Year-Month so they will sort correctly.

Would like either a pop up that the user could fill in 2023-5, but if this is difficult, then a input on Sheet 3 cell a1 could be entered and the VBA would use this as the input,

 

[DanteAmor]

Try this:

Sub DeleteRows()
  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("Sheet1").Range("HC:HC")
    .Replace ym, "#N/A", xlWhole
    On Error Resume Next
      .SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
    On Error GoTo 0
  End With
  With Sheets("Sheet2").Range("BE:BE")
    .Replace ym, "#N/A", xlWhole
    On Error Resume Next
      .SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
    On Error GoTo 0
  End With
End Sub

 

[bthumble]

Thanks for the code.

When I run the macro, the pop up asks me for the Year-Month. When I enter 2023-5 and press ok nothing else happens. The macro is copied into the worksheet and not into my PersonalXLSB.

Not sure if this is important but I am using Excel 365.

 

[DanteAmor]

As I asked you before.
What do you have in the cells?
Do you have dates or texts?
In the image I only see 2023-5, but I don't know if it is a formatted date or it is a text "2023-5".

You have to be very specific on this concept, otherwise I can't give you a solution that works for you.

Also check what you requested:

Sheet1 has Year-Month in column HC and
Sheet2 has Year-Month in column BE

 

[bthumble]

The Year-Mo is formatted as General in both Sheet1 (column HC) and Sheet2 (column BE). It is a concatenation of the Year and month columns.

 

[DanteAmor]

Try this:

Sub DeleteRows()
  Dim ym As Variant
  
  ym = InputBox("Enter year-month, ex: 2023-5")
  If ym = "" Then Exit Sub
  
  With Sheets("Sheet1")
    .Range("HC1", .Range("HC" & Rows.Count).End(3)).AutoFilter 1, ym
    .AutoFilter.Range.Offset(1).EntireRow.Delete
    .AutoFilterMode = False
  End With
  With Sheets("Sheet2")
    .Range("BE1", .Range("BE" & Rows.Count).End(3)).AutoFilter 1, ym
    .AutoFilter.Range.Offset(1).EntireRow.Delete
    .AutoFilterMode = False
  End With
End Sub

 

[bthumble]

That worked. Thank you very much. Will save me much time in the future.

 

[bthumble]

Have a follow up question. After the macro runs it turns off the filters on both sheets. Is there a way to leave the filters there>