Deleting rows on multiple sheet

[Giancar]

Hi everyone,

I am trying without results to remove rows on multiple sheet.
First sheet is called FR, second is called IT and third is called ES.

I need to delete for each sheet, all the rows that have in column F (from F3 till the end of the table) a date before today.

Can you please help me?
Thank you in advance

 

[mumps]

Assuming you have a header in F2.

Sub DelRows()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    Dim ws As Worksheet
    For Each ws In Sheets
        LastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        ws.Range("F2:F" & LastRow).AutoFilter Field:=1, Criteria1:="<" & Date
        ws.Range("F3:F" & LastRow).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        If ws.AutoFilterMode = True Then ws.AutoFilterMode = False
    Next ws
    Application.ScreenUpdating = True
End Sub

Please note that this will work on all sheets in the workbook. If you want it to work only on certain sheets, I would need to know the names of those sheets.

 

[Giancar]

Assuming you have a header in F2.

Sub DelRows()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    Dim ws As Worksheet
    For Each ws In Sheets
        LastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        ws.Range("F2:F" & LastRow).AutoFilter Field:=1, Criteria1:="<" & Date
        ws.Range("F3:F" & LastRow).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        If ws.AutoFilterMode = True Then ws.AutoFilterMode = False
    Next ws
    Application.ScreenUpdating = True
End Sub

Please note that this will work on all sheets in the workbook. If you want it to work only on certain sheets, I would need to know the names of those sheets.

Hi,

Unfortunately is not working, I think because some of the sheets that are on this file have different structure.
I have as result of the macro: No cells were found
The names of those sheets are:
1. FR
2. IT
3. ES

Thanks

 

[SpillerBD]

Seems like I've had trouble if the range already had AutoFilter in use.
Also, can you verify your dates are dates and not text-strings that look like date(numbers).

 

[Giancar]

Seems like I've had trouble if the range already had AutoFilter in use.
Also, can you verify your dates are dates and not text-strings that look like date(numbers).

Dates are in custom format so I assume are not text-strings.
I tried to set them with a date format, running again the macro but still not working.

Don't you think the problem may be in other sheets where I do not have those kind of data? Because in some sheets I have other stuff (general text instruction).

 

[SpillerBD]

If you get an error, yes.
But if you do have other sheets, you will want to have the macro run on only the 3 you're looking to change.
You could merely add a qualifying statement to check for any of the three valid ws.

 

[mumps]

As I mentioned in my response, if you want to have the macro work on only certain sheets, you will need to give me the names of those sheets.

 

[Giancar]

As I mentioned in my response, if you want to have the macro work on only certain sheets, you will need to give me the names of those sheets.

Hi,

But I have mentioned the names of the sheets already twice

The names of those sheets are:
1. FR
2. IT
3. ES

 

[mumps]

Try:

Sub DelRows()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    Dim ws As Worksheet
    For Each ws In Sheets(Array("FR", "IT", "ES"))
        LastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        ws.Range("F2:F" & LastRow).AutoFilter Field:=1, Criteria1:="<" & Date
        ws.Range("F3:F" & LastRow).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        If ws.AutoFilterMode = True Then ws.AutoFilterMode = False
    Next ws
    Application.ScreenUpdating = True
End Sub

 

[Giancar]

Hi guys,

Unfortunately previous answers gave errors, but I think I have found a way a easier way to get the result, but I am still not able to solve this problem. I hope you can help me.
Below how the table is:
---A-----B-----C-----D-----E-----F-----G
1
2 -----------------HEADER ----HEADER---
3-------------------Name ---------date ----
4 ------------------Name ---------date ----
5
till the end of the table

Basically when the date (column F, from data are starting from F3, F2 is header) is "before today", names on column D are strikethrough.
So instead of a Macro that delete rows with F3 before today, that previously gave us errors (probably because of the format of the date), I think can be easier to have a Macro that delete rows, if Names in column F are crossed.
What do you think? I am trying all the combo but still nothing.

I have also managed to edit this doc so instead of actioning the macro on different sheets, it's OK to have it on the Active worksheet (that is the only sheet the doc has).

Thank you