Hello,
I have the following code to delete rows with old data. Below are all the lines pertaining to the functionality I'm asking about, but please note that this is a very small piece of a much larger macro.
The data is considered "old" if it matches the month entered via a user input box (U2) and is from the year before the year entered in a different user box (U1). It's not working, I think because of the "and" statement. Any help with this would be appreciated!
Dim r As Integer
Dim U1 As String
Dim U2 As String
U1 = InputBox("What four-digit year of data are you entering? For example, '2019'")
U2 = InputBox("What two digit month of data are you entering? For example, March would be '03'")
'Delete rows from the previous year of the month of data that was just added.
For r = ThisWorkbook.Sheets("Overall User Data").UsedRange.Rows.Count To 1 Step -1
If Cells(r, "A") = U1 - 1 And Cells(r, "B") = U2 Then
ThisWorkbook.Sheets("Overall User Data").Rows(r).EntireRow.Delete
End If
Next
NOTE: Even better would be if I could skip the input boxes completely. To do that, I would need the macro to look at the year (column A) and month (column B) of data that was entered (from a pulled report) in the "New Data Add" tab before it moves it into the "Overall User Data" tab. Then, it could delete the same month / previous year of the full data set in the "Overall User Data" tab once it's moved.
Another option - it wouldn't need to look at the data while it's still in the "New Data Add" tab. It could simply move that data from the "New Data Add" tab to the "Overall User Data" tab, which it already does, and check that entire data set for any instance of data with the same month and two different years, and delete any line with the older year.
These are a bit too complicated for me (since I couldn't even get the input boxes to work!), so I know they can be done, but don't know how to do them.
Thanks so much for your help!
I have the following code to delete rows with old data. Below are all the lines pertaining to the functionality I'm asking about, but please note that this is a very small piece of a much larger macro.
The data is considered "old" if it matches the month entered via a user input box (U2) and is from the year before the year entered in a different user box (U1). It's not working, I think because of the "and" statement. Any help with this would be appreciated!
Dim r As Integer
Dim U1 As String
Dim U2 As String
U1 = InputBox("What four-digit year of data are you entering? For example, '2019'")
U2 = InputBox("What two digit month of data are you entering? For example, March would be '03'")
'Delete rows from the previous year of the month of data that was just added.
For r = ThisWorkbook.Sheets("Overall User Data").UsedRange.Rows.Count To 1 Step -1
If Cells(r, "A") = U1 - 1 And Cells(r, "B") = U2 Then
ThisWorkbook.Sheets("Overall User Data").Rows(r).EntireRow.Delete
End If
Next
NOTE: Even better would be if I could skip the input boxes completely. To do that, I would need the macro to look at the year (column A) and month (column B) of data that was entered (from a pulled report) in the "New Data Add" tab before it moves it into the "Overall User Data" tab. Then, it could delete the same month / previous year of the full data set in the "Overall User Data" tab once it's moved.
Another option - it wouldn't need to look at the data while it's still in the "New Data Add" tab. It could simply move that data from the "New Data Add" tab to the "Overall User Data" tab, which it already does, and check that entire data set for any instance of data with the same month and two different years, and delete any line with the older year.
These are a bit too complicated for me (since I couldn't even get the input boxes to work!), so I know they can be done, but don't know how to do them.
Thanks so much for your help!
