Macro to delete rows containing certain values

kekstrom

New Member
Joined
Aug 1, 2011
Messages
29
Hello, very new to VBA and excel macros and looking for some help. Hopefully this is pretty easy.

Values start in row 2, row 1 has filters.

Filter Column P and remove any row containing a date before 8/1/2016.

Filter Column N and remove any row greater than or equal to 2.0 and less than -.2

Filter Column K by color yellow.

copy row 3 all the way over and all the way down.

This is extra, buy if possible I would like to do the following...

Paste the copied data into sheet titled 'Data History', but the data needs to be pasted where after where the last data was pasted. (This is something I need to do daily and can't be pasting over past data)


THanks, please advise if you need more details on what my sheets look like.
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Lets forget about the last part for now and just try to get the rows removed! :biggrin:

Thank you

Also, tried to post a screen shot of my spreadsheet but I can't upload from work, sorry.
 
Upvote 0
Hello,

Here is code that should do what you describe.

It also incorporates the few clarifications you made when you sent me a copy of the workbook:

1. Doesn't need to filter for Yellow color
2. Data from Row 3 to last roow of Source data sheet should be deleted after copying to History Sheet.
3. Rows that have #VALUE! in Col N should be copied as well as rows meeting the date criteria.

Rich (BB code):
Sub Filter_Copy_Delete()
    Dim lRowSource As Long, lRowTarget As Long, lCol As Long
    Dim rngToCopy As Range
    Application.ScreenUpdating = False
    On Error Resume Next
 
    With Sheets("Source Data")
        .AutoFilterMode = False
        lRowSource = .Cells.Find(What:="*", After:=.Range("A1"), _
            SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        lCol = .Cells.Find(What:="*", After:=.Range("A1"), _
            SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
 
        With .Range("A1", .Cells(lRowSource, lCol))
           .AutoFilter Field:=16, Criteria1:=">=8/1/2016", _
                Operator:=xlOr, Criteria2:="#VALUE!"
            .AutoFilter Field:=14, Criteria1:="<2", _
                Operator:=xlAnd, Criteria2:=">=-.2"
            Set rngToCopy = .Offset(2).Resize(.Rows.Count - 2) _
                .SpecialCells(xlCellTypeVisible)
        End With
    End With
    If Not rngToCopy Is Nothing Then
        rngToCopy.Copy
        With Sheets("Data History")
            lRowTarget = .Cells.Find(What:="*", After:=.Range("A1"), _
                SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            .Cells(lRowTarget + 1, 1).PasteSpecial (xlPasteValues)
        End With
    End If
    With Sheets("Source Data")
         .AutoFilterMode = False
        '---Remove the ' to delete all but top 2 rows of Source Data sheet
      '    .Rows("3:" & lRowSource).EntireRow.Delete
  End With
    Set rngToCopy = Nothing
End Sub
 
Last edited:
Upvote 0
Thanks again Jerry, you/I really amazed some of my coworkers with the CUSIP Generator that you made...

No one even knew how the check digit was calculated or that it was possible to do this without having to run the cusips through bloomberg! AWESOME!



This macro works great also, saving time through macros is great, I need to start improving my own skill. I hope you don't mind me coming to you occasionally in the meantime :biggrin:
 
Upvote 0

Forum statistics

Threads
1,224,602
Messages
6,179,843
Members
452,948
Latest member
UsmanAli786

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