I NEED EXCEL EXPERTS!

Question

IF THERE A WAY I CAN HAVE OLD DATES CLEAR OUT ONCE THE DAY HAS HAPPENED?

Answer 1

this is all great however how do I get to these steps. I am not very excel saavy. Can you provided step by step and how to get to each screen. Do I need to create a new workbook?

OK,

Please, try the following steps:

1. Make a copy of your workbook and save it as an Excel Macro-Enabled Workbook (*.xlsm)

2. On your keyboard, press: ALT+F11. This will prompt the VBA edit panel. Then Double-click on "Thisworkbook"
3. Copy and paste the code into the Workbook's Open Procedure VBA editor as shown in the picture below.

This is the code:

Private Sub Workbook_Open()

Dim sh As Worksheet

Dim Lr As Long

Dim x As Long

Dim cnt As Long

Application.ScreenUpdating = False

''' Loop thru all the sheets in the workbook

For Each sh In ThisWorkbook.Worksheets

        With sh

                ''' Find the last used row in column E

                Lr = .Range("E" & Rows.Count).End(xlUp).Row

                For x = 7 To Lr

                        If IsDate(.Range("E" & x)) Then

                                ''' Expiration date checking and data removing

                                If .Range("E" & x).Value <= Date Then

                                    .Range("E" & x).EntireRow.Delete

                                    cnt = cnt + 1   ''' counting

                                End If

                        End If

                Next x

        End With

Next sh

Application.ScreenUpdating = True

'''Reporting

MsgBox Prompt:="Total Absence removed:= " & cnt, Title:="REMOVAL REPORT - DATE: " & Date

End Sub

4. Then save all the changes and close the file.
5. Open the file/workbook once again, and the macro code will execute upon opening.

IMPORTANT NOTE:

The code was written assuming the following:

1. All the sheet tabs have a similar structure, with the "Occasion Absence Removed" field in column E as the field that calculates the 9-month date from column A.
2. The data values start in row 7 downwards.

I hope this helps you and gives a solution to your problem

Do let me know if you need more help

Jeovany

Answer 2

this is all great however how do I get to these steps. I am not very excel saavy. Can you provided step by step and how to get to each screen. Do I need to create a new workbook?

Answer 3

Thank you for the additional details

I partially reproduced your scenario for illustration purposes

The following VBA code was written assuming the following:

1. All the sheet tabs have a similar structure, with the "Occasion Absence Removed" field in column E as the field that calculates the 9-month date from column A.
2. The data values start in row 7 downwards.

The sample picture highlights the three dates on which rows must be removed.

The code will execute when the Workbook opens.

It will loop through each sheet in the workbook, then loop through all the used cells in column E, checking if the date in the cell is less than or equal to the current date, and delete the entire row with data if it is true.

The code shows a pop-up reporting message

The picture shows where the code must be placed.

Here is the code:

Private Sub Workbook_Open()

Dim sh As Worksheet

Dim Lr As Long

Dim x As Long

Dim cnt As Long

Application.ScreenUpdating = False

''' Loop thru all the sheets in the workbook

For Each sh In ThisWorkbook.Worksheets

        With sh

                ''' Find the last used row in column E

                Lr = .Range("E" & Rows.Count).End(xlUp).Row

                For x = 7 To Lr

                        If IsDate(.Range("E" & x)) Then

                                ''' Expiration date checking and data removing

                                If .Range("E" & x).Value <= Date Then

                                    .Range("E" & x).EntireRow.Delete

                                    cnt = cnt + 1   ''' counting

                                End If

                        End If

                Next x

        End With

Next sh

Application.ScreenUpdating = True

'''Reporting

MsgBox Prompt:="Total Absence removed: " & cnt, Title:="REMOVAL REPORT - DATE: " & Date

End Sub

I hope this helps you and gives a solution to your problem

Do let me know if you need more help

On the other hand,

If the answer helped you.

Please, consider marking this thread as answered.

It would help others in the community with similar questions or problems.

Thank you in advance.

Regards

Jeovany

Answer 4

YOU SEE THE DATE ON THE LEFT IS WHAT WE ENTER, THEN HOWEVER MANY POINTS IS ENTERED UNDER THE OCCASIONS ROW, 9 MONTHS FROM THAT DATE IS WHEN THE OCCASION POINT IS REMOVED.  I JUST WANT THE INFORMATION TO AUTOMATICALLY CLEAR EVERY TIME YOU OPEN THE WORKBOOK INSTEAD OF GOING INTO EACH AND EVERY TAB INDIVIDUALLY.  THANK YOU :)

Answer 5

IF THERE A WAY I CAN HAVE OLD DATES CLEAR OUT ONCE THE DAY HAS HAPPENED?

YES, sure.

However, we need more details about your scenario and goals, in order to provide you with a viable solution.

Please, post pictures of your table(s), and explain to us what you mean by "...clear out..."?

Regards

Jeovany