Deleting rows on multiple sheet

G

Giancar

Board Regular
Joined
Nov 29, 2017
Messages
52
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
 
Sort by date Sort by votes
It is always easier to help and test possible solutions if we could work with your actual file. Perhaps you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells and worksheets. If the workbook contains confidential information, you could replace it with generic data.
 
Upvote 0

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
mumps said:
It is always easier to help and test possible solutions if we could work with your actual file. Perhaps you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells and worksheets. If the workbook contains confidential information, you could replace it with generic data.
Click to expand...

Hey Mumps,

Thank you for your patience. I thought it was easier if explained in that way but I totally see your point. Link: https://www.dropbox.com/s/9s1bloa4p5qefyf/18-12.xlsx?dl=0
Below details:
1) This is a Gdoc converted file, but I need always to work on Excel, and it gets update everyday
2) When I need to work on it, I copy and paste values only (in the same position on the worksheet) for all the main sheets (FR, IT, ES), because it's difficult for many reasons to work on formulas

What I am trying to do is:
1) For sheet ES, FR and IT an automatic order:
a) Sort by Column R (Largest to Smallest), if value is the same sort by column S (Largest to smallest)
b) Column D must not be strikethrough (or Column F needs to be today or bigger)
c) I need to divide all the rows in sub categories:
- 1st group: Column J is A OR B OR C OR D (just an example) AND Column H is hotel (then I need to copy the Column A of the results given in a different sheet)
- 2nd group: Column J is 1 OR 2 OR 3 OR 4 OR 5 (just an example) AND Column H is hotel (then I need to copy the Column A of the results given in a different sheet) - 3rd group: Column J is Q OR W OR E OR R OR T (just an example) AND Column H is hotel (then I need to copy the Column A of the results given in a different sheet)
- 4th group: Column H is package (then I need to copy the Column A of the results given in a different sheet)
-5th group: Column G is A or B or C AND Column H is hotel (then I need to copy the Column A of the results given in a different sheet)
- 6th group: Column G is D, AND Column I is not Y. (then I need to copy the Column A of the results given in a different sheet)

So each of these groups will be: not crossed, sort by Column R (largest to smallest) and then by column S.

For some of these criteria I am OK, but still struggling with some of them. I hope I have been clear enough :)
 
Upvote 0
Instead of using letters or numbers, could you use actual values from your data to describe the filter criteria in each of the 6 groups? This way it's easier for me to test any possible solution on the actual data in the file. Also, do you want to copy each group to its own separate sheet or all 6 groups to one sheet? If you want separate sheets for each group, what names do you want for each group's sheet?
 
Upvote 0
mumps said:
Instead of using letters or numbers, could you use actual values from your data to describe the filter criteria in each of the 6 groups? This way it's easier for me to test any possible solution on the actual data in the file. Also, do you want to copy each group to its own separate sheet or all 6 groups to one sheet? If you want separate sheets for each group, what names do you want for each group's sheet?
Click to expand...

c) I need to divide all the rows in sub categories:
- 1st group: Column J is "Tuscany" OR "Trentino-Alto Adige" OR "Emilia-Romagna" OR "Veneto" OR "Latium" OR "Lombardy" AND Column H is "Hotel" (then I need to copy the Column A of the results given in a different sheet)
- 2nd group: Column J is "Sicily" OR "Aosta Valley" OR "Campania" OR "Liguria" OR "Basilicata" OR "Marches" (just an example) AND Column H is "Hotel" (then I need to copy the Column A of the results given in a different sheet) - 3rd group: Column J is "Piedmont" OR "Apulia" OR "Umbria" OR "Abruzzo" OR "Sardinia" OR "Calabria" (just an example) AND Column H is "Hotel" (then I need to copy the Column A of the results given in a different sheet)
- 4th group: Column H is "Package" (then I need to copy the Column A of the results given in a different sheet)
-5th group: Column G is "LONG_HAUL" or "MIDDLE_HAUL" AND Column H is "Hotel" (then I need to copy the Column A of the results given in a different sheet)
- 6th group: Column G is "EUROPE", AND Column I is not "Italy". (then I need to copy the Column A of the results given in a different sheet)

These groups are only for sheet "IT" cause then for "FR" and "ES" I have different groups, but then if it works for IT, I will replace with different groups of course.
Regarding the sheet where paste those groups is fine to create another sheet called "IT GROUPS", no need of a sheet for each group
 
Upvote 0
If the groups never change, if you send me the groups for FR and ES, then we can have the macro do all three sheets at one time unless you want to do the sheets separately.
 
Upvote 0
mumps said:
If the groups never change, if you send me the groups for FR and ES, then we can have the macro do all three sheets at one time unless you want to do the sheets separately.
Click to expand...

I would prefer to do sheets seperately. In case I will create a Macro to call other Macros
 
Upvote 0
Here is the macro for the "IT" sheet. Be patient when you run it. It may take 10 to 15 seconds to run. Make sure that you have created a sheet named "IT GROUPS".
Code: 
Sub CopyRangeIT()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("IT").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Sheets("IT").Sort.SortFields.Clear
    Sheets("IT").Sort.SortFields.Add Key:=Range("R1:R" & LastRow), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    Sheets("IT").Sort.SortFields.Add Key:=Range("S1:S" & LastRow), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With Sheets("IT").Sort
        .SetRange Range("A1:AR" & LastRow)
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Sheets("IT").Range("A1:AR" & LastRow).AutoFilter Field:=6, Criteria1:=">=" & Date
    Sheets("IT").Range("A1:AR" & LastRow).AutoFilter Field:=10, Criteria1:=Array( _
        "Trentino-Alto Adige", "Tuscany", "Emilia-Romagna", "Veneto", "Latium", "Lombardy"), Operator:=xlFilterValues
    Sheets("IT").Range("A1:AR" & LastRow).AutoFilter Field:=8, Criteria1:="Hotel"
    Sheets("IT").Range("A2:AR" & LastRow).SpecialCells(xlCellTypeVisible).Copy Sheets("IT GROUPS").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
    If Sheets("IT").AutoFilterMode = True Then Sheets("IT").AutoFilterMode = False
    Sheets("IT").Range("A1:AR" & LastRow).AutoFilter Field:=10, Criteria1:=Array( _
        "Sicily", "Aosta Valley", "Campania", "Liguria", "Basilicata", "Marches"), Operator:=xlFilterValues
    Sheets("IT").Range("A1:AR" & LastRow).AutoFilter Field:=8, Criteria1:="Hotel"
    Sheets("IT").Range("A2:AR" & LastRow).SpecialCells(xlCellTypeVisible).Copy Sheets("IT GROUPS").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
    If Sheets("IT").AutoFilterMode = True Then Sheets("IT").AutoFilterMode = False
    Sheets("IT").Range("A1:AR" & LastRow).AutoFilter Field:=10, Criteria1:=Array( _
        "Piedmont", "Apulia", "Umbria", "Abruzzo", "Sardinia", "Calabria"), Operator:=xlFilterValues
    Sheets("IT").Range("A1:AR" & LastRow).AutoFilter Field:=8, Criteria1:="Hotel"
    Sheets("IT").Range("A2:AR" & LastRow).SpecialCells(xlCellTypeVisible).Copy Sheets("IT GROUPS").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
    If Sheets("IT").AutoFilterMode = True Then Sheets("IT").AutoFilterMode = False
    Sheets("IT").Range("A1:AR" & LastRow).AutoFilter Field:=8, Criteria1:="Package"
    Sheets("IT").Range("A2:AR" & LastRow).SpecialCells(xlCellTypeVisible).Copy Sheets("IT GROUPS").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
    If Sheets("IT").AutoFilterMode = True Then Sheets("IT").AutoFilterMode = False
    Sheets("IT").Range("A1:AR" & LastRow).AutoFilter Field:=7, Criteria1:=Array( _
        "LONG_HAUL", "MIDDLE_HAUL"), Operator:=xlFilterValues
    Sheets("IT").Range("A1:AR" & LastRow).AutoFilter Field:=8, Criteria1:="Hotel"
    Sheets("IT").Range("A2:AR" & LastRow).SpecialCells(xlCellTypeVisible).Copy Sheets("IT GROUPS").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
    If Sheets("IT").AutoFilterMode = True Then Sheets("IT").AutoFilterMode = False
    Sheets("IT").Range("A1:AR" & LastRow).AutoFilter Field:=7, Criteria1:="EUROPE"
    Sheets("IT").Range("A1:AR" & LastRow).AutoFilter Field:=9, Criteria1:="<>Italy"
    Sheets("IT").Range("A2:AR" & LastRow).SpecialCells(xlCellTypeVisible).Copy Sheets("IT GROUPS").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
    If Sheets("IT").AutoFilterMode = True Then Sheets("IT").AutoFilterMode = False
    Application.ScreenUpdating = True
End Sub
 
Last edited:
Upvote 0
mumps said:
Here is the macro for the "IT" sheet. Be patient when you run it. It may take 10 to 15 seconds to run. Make sure that you have created a sheet named "IT GROUPS".
Code: 
Sub CopyRangeIT()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("IT").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Sheets("IT").Sort.SortFields.Clear
    Sheets("IT").Sort.SortFields.Add Key:=Range("R1:R" & LastRow), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    Sheets("IT").Sort.SortFields.Add Key:=Range("S1:S" & LastRow), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With Sheets("IT").Sort
        .SetRange Range("A1:AR" & LastRow)
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Sheets("IT").Range("A1:AR" & LastRow).AutoFilter Field:=6, Criteria1:=">=" & Date
    Sheets("IT").Range("A1:AR" & LastRow).AutoFilter Field:=10, Criteria1:=Array( _
        "Trentino-Alto Adige", "Tuscany", "Emilia-Romagna", "Veneto", "Latium", "Lombardy"), Operator:=xlFilterValues
    Sheets("IT").Range("A1:AR" & LastRow).AutoFilter Field:=8, Criteria1:="Hotel"
   [U] Sheets("IT").Range("A2:AR" & LastRow).SpecialCells(xlCellTypeVisible).Copy Sheets("IT GROUPS").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)[/U]
    If Sheets("IT").AutoFilterMode = True Then Sheets("IT").AutoFilterMode = False
    Sheets("IT").Range("A1:AR" & LastRow).AutoFilter Field:=10, Criteria1:=Array( _
        "Sicily", "Aosta Valley", "Campania", "Liguria", "Basilicata", "Marches"), Operator:=xlFilterValues
    Sheets("IT").Range("A1:AR" & LastRow).AutoFilter Field:=8, Criteria1:="Hotel"
    Sheets("IT").Range("A2:AR" & LastRow).SpecialCells(xlCellTypeVisible).Copy Sheets("IT GROUPS").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
    If Sheets("IT").AutoFilterMode = True Then Sheets("IT").AutoFilterMode = False
    Sheets("IT").Range("A1:AR" & LastRow).AutoFilter Field:=10, Criteria1:=Array( _
        "Piedmont", "Apulia", "Umbria", "Abruzzo", "Sardinia", "Calabria"), Operator:=xlFilterValues
    Sheets("IT").Range("A1:AR" & LastRow).AutoFilter Field:=8, Criteria1:="Hotel"
    Sheets("IT").Range("A2:AR" & LastRow).SpecialCells(xlCellTypeVisible).Copy Sheets("IT GROUPS").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
    If Sheets("IT").AutoFilterMode = True Then Sheets("IT").AutoFilterMode = False
    Sheets("IT").Range("A1:AR" & LastRow).AutoFilter Field:=8, Criteria1:="Package"
    Sheets("IT").Range("A2:AR" & LastRow).SpecialCells(xlCellTypeVisible).Copy Sheets("IT GROUPS").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
    If Sheets("IT").AutoFilterMode = True Then Sheets("IT").AutoFilterMode = False
    Sheets("IT").Range("A1:AR" & LastRow).AutoFilter Field:=7, Criteria1:=Array( _
        "LONG_HAUL", "MIDDLE_HAUL"), Operator:=xlFilterValues
    Sheets("IT").Range("A1:AR" & LastRow).AutoFilter Field:=8, Criteria1:="Hotel"
    Sheets("IT").Range("A2:AR" & LastRow).SpecialCells(xlCellTypeVisible).Copy Sheets("IT GROUPS").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
    If Sheets("IT").AutoFilterMode = True Then Sheets("IT").AutoFilterMode = False
    Sheets("IT").Range("A1:AR" & LastRow).AutoFilter Field:=7, Criteria1:="EUROPE"
    Sheets("IT").Range("A1:AR" & LastRow).AutoFilter Field:=9, Criteria1:="<>Italy"
    Sheets("IT").Range("A2:AR" & LastRow).SpecialCells(xlCellTypeVisible).Copy Sheets("IT GROUPS").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
    If Sheets("IT").AutoFilterMode = True Then Sheets("IT").AutoFilterMode = False
    Application.ScreenUpdating = True
End Sub
Click to expand...

Hey Mumps,

Sorry for late reply and many thanks for your post.
Unfortunately this macro is still not working. I have created the sheet IT GROUPS but it gave me an error to the line underscored on the quote above.
Do you have any idea or further suggestions?

Happy new year!
 
Upvote 0
Click here to download your file. I ran the macro and it worked properly. Give it a try.
 
Upvote 0
I have downloaded your file and I have run the Macro. I still have Run-time error '1004': No cells were found.
You created already the sheet "IT GROUPS", so I have just run the macro....with above error.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Delete certain N\A
2
Replies
13
Views
286
Joe4
Joe4
A
  • Solved
Need VBA to automatically copy paste rows based factory selected
Replies
8
Views
266
Alex Blakenburg
A
A
  • Solved
Auto Serial Number in a Cell
2 3
Replies
23
Views
382
arijitirf
A
F
  • Question
Copy to Multiple Worksheets With Different Arranged Headers ROW by ROW
Replies
1
Views
113
Cubist
Cubist
O
  • Question
Printing multiple times with changing data
Replies
1
Views
273
orzys
O

Forum statistics

Threads
1,216,119
Messages
6,128,944
Members
449,480
Latest member
yesitisasport

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top