Deleting worksheet(s) when certain conditions are met

user_15425462 0
Viewed 32

I have a file A.xlsx that contains time series data with multiple worksheets. Column D indicates Year and Column E has the Month. Both Year and Month are in numbers. I wrote a code to delete any worksheets that have data prior to 2020.9 (September). However, the results also contain some sheets that have data before this time point. Are there any suggestions on how to improve the code?

Sub Remove_worksheets()
Dim A As workbook: Set A = Workbooks("A.xlsx")
Dim sh As Worksheet

Application.ScreenUpdating = False
Application.DisplayAlerts = False

For Each sh In A.Worksheets
    If sh.Range("D1").End(xlDown) < 2020 And sh.Range("E1").End(xlDown) < 9 Then
      sh.Delete
    End If
Next sh

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub
multiple-conditions time-series if-statement vba excel
1 Answers

Your current code only deletes those sheets where BOTH conditions are satisfied: (1) year is below 2020 AND (2) month is below 9. Such combination allows all September-December to slip through no matter what year.

You should replace

If sh.Range("D1").End(xlDown) < 2020 And sh.Range("E1").End(xlDown) < 9 Then

with something like

If sh.Range("D1").End(xlDown) < 2020 _
   Or (sh.Range("D1").End(xlDown) = 2020 And sh.Range("E1").End(xlDown) < 9) Then
user_27535010
Related