Auto deleting in excel

[PlumGr8]

So, i got some great help a couple years ago in getting a VB Macro to run to delete days within an excel sheet that were X amount of days old. It was also setup to count and add certain even together. So i am working on using that VB for another sheet and am try to modify it to work and having an issue. In the original one column A had a standard date 20220101 and it would look for dates older than they and delete. No when they do it, they will merger the entire row columns A-L and enter the date as "Monday, January 3, 2022". Does the VB have to be adjusted to see the date that way? Also, in the original VB i was tracking trucks going from "Site 1", "site 2" etc, so as it would delete it would add to another cell to keep track of the total as well, but not in this sheet they use arrows "↑" and "↓". Does VB recognize using those arrows?

Here is a copy of the VB i had entered for now, this is definitely above me, so trying to make it work so can use any help thanks.

Private Sub Workbook_Open()

Sheets("Delivery").Unprotect

DaysOld = 75

CheckRow = 4 'This is the first row to check - it assumes that rows 1-2-3 are headers

RecordsFound = 0 'This is a counter of the number of rows deleted

Application.EnableEvents = False

Application.ScreenUpdating = False

With Sheets("Delivery")

Do 'Go through records, if the record is old enough to delete then delete.

'Stop when column A is blank

If .Range("A" & CheckRow).Value = "" Then Exit Do

If .Range("A" & CheckRow).Value < (Now - DaysOld) Then

'This record needs to be deleted - check if it contains any Job Sites and update the totals

If .Range("E" & CheckRow).Value = "↑" Then .Range("AA5").Value = .Range("AA5").Value + 1

If .Range("E" & CheckRow).Value = "↓" Then .Range("AF5").Value = .Range("AF5").Value + 1



'Then delete the record

.Range("A" & CheckRow & ":M" & CheckRow).Delete (xlShiftUp)

RecordsFound = RecordsFound + 1

Else

CheckRow = CheckRow + 1

End If

Loop

End With

Application.ScreenUpdating = True

Application.EnableEvents = True

Sheets("Delivery").Protect



End Sub

 

[Trevor_S]

Hi - I've formatted your code, hopefully it will then be easier to see the loops, etc.

Private Sub Workbook_Open()
Sheets("Delivery").Unprotect
DaysOld = 75
CheckRow = 4 'This is the first row to check - it assumes that rows 1-2-3 are headers
RecordsFound = 0 'This is a counter of the number of rows deleted
Application.EnableEvents = False
Application.ScreenUpdating = False
With Sheets("Delivery")
    Do 'Go through records, if the record is old enough to delete then delete.
        'Stop when column A is blank
        If .Range("A" & CheckRow).Value = "" Then Exit Do
        If .Range("A" & CheckRow).Value < (Now - DaysOld) Then
            'This record needs to be deleted - check if it contains any Job Sites and update the totals
            If .Range("E" & CheckRow).Value = "↑" Then .Range("AA5").Value = .Range("AA5").Value + 1
            If .Range("E" & CheckRow).Value = "↓" Then .Range("AF5").Value = .Range("AF5").Value + 1
            'Then delete the record
            .Range("A" & CheckRow & ":M" & CheckRow).Delete (xlShiftUp)
            RecordsFound = RecordsFound + 1
        Else
            CheckRow = CheckRow + 1
        End If
    Loop
End With
Application.ScreenUpdating = True
Application.EnableEvents = True
Sheets("Delivery").Protect
End Sub

The date format in itself isn't an issue, provided that Excel still recognises it as a date rather than text. In other words, it needs to be a date which just appears like that due to the settings in Format > Cells. To test this, you can try temporarily changing the formatting of the cell, and see if the appearance of the date changes.

Generally, a difficulty with merged cells is that the value of the cell is only held against the reference of the top left cell in the merged area. If columns A to L are merged, does this mean that the merged cell contains everything which would otherwise have been in the individual columns A to L, rather than just a date? If that's the case, it's going to be very difficult to get the macro to be able to recognise the date element, particularly given that the format being used will result in the length of the date element varying between rows and (by being text) the date itself could be open to typing / spelling errors.

Are any rows also merged - for example two or more rows relating to the same date? If so, this could cause issues, as the macro currently detects the end of the records by looking for a blank in column A. Say rows 5-7 are merged with a new record in row 8 - from the macro's perspective only rows 5 and 8 contain a date, with rows 6 and 7 being blank. The macro will stop at row 6 and not process the rest. It may be possible to work around this by:

• using another way to detect the end of the records, and
• storing the latest date found so that can be tested when column A is blank.

But none of this will work if there is more than just a date in the merged columns A to L.

I assume that the arrow characters are obtained by using some form of font - such as Wingdings or Symbol? If so, find out what these characters "really" are by temporarily changing the font in one of those cells to something more normal, and use those "real" characters in the macro.

While there are many good reasons for not using merged cells, and I would always advise against using them - probably the main issue that would be very difficult to work around will be if you have more than just the date in the merged column A to L.

 

[PlumGr8]

Hi - I've formatted your code, hopefully it will then be easier to see the loops, etc.

Private Sub Workbook_Open()
Sheets("Delivery").Unprotect
DaysOld = 75
CheckRow = 4 'This is the first row to check - it assumes that rows 1-2-3 are headers
RecordsFound = 0 'This is a counter of the number of rows deleted
Application.EnableEvents = False
Application.ScreenUpdating = False
With Sheets("Delivery")
    Do 'Go through records, if the record is old enough to delete then delete.
        'Stop when column A is blank
        If .Range("A" & CheckRow).Value = "" Then Exit Do
        If .Range("A" & CheckRow).Value < (Now - DaysOld) Then
            'This record needs to be deleted - check if it contains any Job Sites and update the totals
            If .Range("E" & CheckRow).Value = "↑" Then .Range("AA5").Value = .Range("AA5").Value + 1
            If .Range("E" & CheckRow).Value = "↓" Then .Range("AF5").Value = .Range("AF5").Value + 1
            'Then delete the record
            .Range("A" & CheckRow & ":M" & CheckRow).Delete (xlShiftUp)
            RecordsFound = RecordsFound + 1
        Else
            CheckRow = CheckRow + 1
        End If
    Loop
End With
Application.ScreenUpdating = True
Application.EnableEvents = True
Sheets("Delivery").Protect
End Sub

The date format in itself isn't an issue, provided that Excel still recognises it as a date rather than text. In other words, it needs to be a date which just appears like that due to the settings in Format > Cells. To test this, you can try temporarily changing the formatting of the cell, and see if the appearance of the date changes.

Generally, a difficulty with merged cells is that the value of the cell is only held against the reference of the top left cell in the merged area. If columns A to L are merged, does this mean that the merged cell contains everything which would otherwise have been in the individual columns A to L, rather than just a date? If that's the case, it's going to be very difficult to get the macro to be able to recognise the date element, particularly given that the format being used will result in the length of the date element varying between rows and (by being text) the date itself could be open to typing / spelling errors.

Are any rows also merged - for example two or more rows relating to the same date? If so, this could cause issues, as the macro currently detects the end of the records by looking for a blank in column A. Say rows 5-7 are merged with a new record in row 8 - from the macro's perspective only rows 5 and 8 contain a date, with rows 6 and 7 being blank. The macro will stop at row 6 and not process the rest. It may be possible to work around this by:

• using another way to detect the end of the records, and
• storing the latest date found so that can be tested when column A is blank.

But none of this will work if there is more than just a date in the merged columns A to L.

I assume that the arrow characters are obtained by using some form of font - such as Wingdings or Symbol? If so, find out what these characters "really" are by temporarily changing the font in one of those cells to something more normal, and use those "real" characters in the macro.

While there are many good reasons for not using merged cells, and I would always advise against using them - probably the main issue that would be very difficult to work around will be if you have more than just the date in the merged column A to L.

So when they merge the row across A to M, they just merge it and enter the date manually. Mostly just because for them its faster than merging, formatting and typing the date and letting excel handle it. So, it seems having it detect the date like i had before will be more of a pain.

Would it be easier to set a delete value for total cells? Like, once the data exceeds 100 rows, or 250 rows etc it will auto delete, move the data up and such like i had before? And or is there a way to say delete X amount of cells past a set value up to the next "merged" row? Or is that too much of a pain?

So the arrows i just used the "alt 24 and alt 25" functions. Works in the excel sheet, and for the drop down list function, but being off when i try to enter in as the macro.

Figure will try and get this sorted, then i am still trying to get that other macro done that we had to track MX being done. I'll get to that part next haha.

 

[Trevor_S]

So with this merging - is there anything else in this merged cell? Are we saying that there are 12 columns (or 13, depending on whether it's "A to L" or "A to M") just containing one date - or are there other things in there too? Perhaps you could show an extract here?

For the arrows, those characters can't be displayed in the Visual Basic Editor. You could try changing the macro to look for them by ChrW character number (available from the Windows Character Map) - 2191 for up and 2193 for down - i.e.:

            If .Range("E" & CheckRow).Value = ChrW(2191) Then .Range("AA5").Value = .Range("AA5").Value + 1
            If .Range("E" & CheckRow).Value = ChrW(2193) Then .Range("AF5").Value = .Range("AF5").Value + 1

 

[PlumGr8]

So the merged cell, just have the days. All the data like the original form is in columns A to M, L & M are "usually" hidden and only used for certain times. But when they do the dates since they just go by the day, and not as precisely as we do it, they just highlight say row 10 all the way across, format it "green" and manually type in the day so that it says "Monday, January 3, 2022".

I will try that above for the character map. Since i do have those arrows in another cell to track up & down, i did try in the formula to recognize cell "Y4" to see if it see its.

 

[PlumGr8]

This is

So with this merging - is there anything else in this merged cell? Are we saying that there are 12 columns (or 13, depending on whether it's "A to L" or "A to M") just containing one date - or are there other things in there too? Perhaps you could show an extract here?

For the arrows, those characters can't be displayed in the Visual Basic Editor. You could try changing the macro to look for them by ChrW character number (available from the Windows Character Map) - 2191 for up and 2193 for down - i.e.:

            If .Range("E" & CheckRow).Value = ChrW(2191) Then .Range("AA5").Value = .Range("AA5").Value + 1
            If .Range("E" & CheckRow).Value = ChrW(2193) Then .Range("AF5").Value = .Range("AF5").Value + 1
[/cod
[/QUOTE]

So this is what i currently have so far.

Private Sub Workbook_Open()

DaysOld = 75
CheckRow = 4 'This is the first row to check - it assumes that rows 1-2-3 are headers
RecordsFound = 0 'This is a counter of the number of rows deleted
Application.EnableEvents = False
Application.ScreenUpdating = False
With Sheets("Delivery Data")
Do 'Go through records, if the record is old enough to delete then before deleting, check for impacts on formulae in col AA and update the value in col AF
'Stop when column A is blank
If .Range("A" & CheckRow).Value = "" Then Exit Do
If .Range("A" & CheckRow).Value < (Now - DaysOld) Then
'This record needs to be deleted - check if it contains any Job Sites and update the totals
If .Range("E" & CheckRow).Value = ChrW(2191) Then .Range("AA6").Value = .Range("AA6").Value + 1
If .Range("E" & CheckRow).Value = ChrW(2193) Then .Range("AA7").Value = .Range("AA7").Value + 1

'Then delete the record
.Range("A" & CheckRow & ":M" & CheckRow).Delete (xlShiftUp)
RecordsFound = RecordsFound + 1
Else
CheckRow = CheckRow + 1
End If
Loop
End With
Application.ScreenUpdating = True
Application.EnableEvents = True


End Sub

 

[Trevor_S]

So are the merged cells actually in each column - for example A5:A7, B5:B7, C5:C7 ... M5:M7? The problem this adds is that if cells are being merged manually it is possible (even if unintentional) that the row ranges may differ between columns.

It sounds that the design of this spreadsheet may be putting its visual appearance to the user as a higher priority than the integrity of the data structure! I personally would not automate any deletions in a spreadsheet with cells merged in this way. There is way too much potential for error!

However if you do decide to go ahead, you still need to check that (regardless of how they appear) whether the dates are actually being recognised by Excel as such. The previous macro wasn't looking for a date in any particular format - it just needed something that it actually recognised as a date. If they are recognised as dates, consider these lines:

'Stop when column A is blank
If .Range("A" & CheckRow).Value = "" Then Exit Do
If .Range("A" & CheckRow).Value < (Now - DaysOld) Then
'This record needs to be deleted - check if it contains any Job Sites and update the totals

The "stop" test needs to be done on a column that will never have merged cells and where every row will contain an entry - let's say that is column "Z" for the example below. Also you need to store the previous date found to use when you get to the second, third, etc. rows in a merged range. Suggest:

'Stop when column Z is blank
If .Range("Z" & CheckRow).Value = "" Then Exit Do
'If column A isn't blank, it's either an individual cell or the first of a merged range.  Store this date and run the checks against that.
If .Range("A" & CheckRow).Value <> "" Then PreviousDate = .Range("A" & CheckRow).Value
If PreviousDate < (Now - DaysOld) Then
'This record needs to be deleted - check if it contains any Job Sites and update the totals

 

[PlumGr8]

Yea they don't make it easy for me to wanting try and automate and makes things easier. Issues is unlike my other sheet where I had more freedom to modify, this one I don't. I am uploading an image of what the sheet looks like. You can see all the dat is kept between col A to M. In col Y to AG I have a couple spots that I am trying to automate like my last sheet to track items. But this one is easier, its just a in and outgoing track. I will play around with what you mentioned above, just wanted to give you a visual reference. IF trying to do it by date is too much of a pain, I can do it just just overall rows. Like set it to maintain a 200 row history, deleting cells over 200 and deleting shifting the rows up. If there was a way to do, delete past 200 row to next merge row that could work too but not necessary.

 

[Trevor_S]

Ok - that's a bit clearer now to see what they've done. They're inserting rows for the merged date, with the data rows below. I'm assuming that the date isn't also repeated in column A of the data rows too.

Getting the macro to check the date definitely won't work. I can see from here that Excel isn't recognising them as dates, as they've spelt "Saturday" incorrectly! They're really not making life easy for themselves - if they want the date in that format, they could have just entered the date and used a custom date format of "dddd dd mmmm yyyy" to display it like that.

It might be possible to do your other suggestion if there is a column (other than A) which will have an entry in every data row except the merged ones - as the gaps in that column will indicate where each block of data starts. I'll give it a bit of thought - won't be able to get chance to do it today though.

 

[PlumGr8]

That was my fault I was trying to type up an example quickly haha. I did think of doing the date function, merging the cells, format them for the date etc. But that's "too" much for most people, they just merge, highlight green and manually type in the date. So it seems like the other idea would be best, just to delete rows past a certain #, which should be fine as well. Like my other sheet, they have spots that keep these cells going for several months even years and the files get too big, corrupted etc, and there's really no need to keep info here past a month or so. So just deleting past a certain row would probably be best. IF there was a way to delete past say row 200, to next identified merged row that would be awesome, but if that is too much of a pain than just by rows should work out well.

And no rush, I have time to tinker with this. Appreciate all the help.