VBA - Delete all rows except rows which contain certain text

Giggs1991

New Member
Joined
Mar 17, 2019
Messages
34
Hi All,

I have a excel workbook with 2 worksheets.
Worksheet A has a very badly formatted data dump from our timesheet tool.
Worksheet B has a list of project names.

I am looking for a vba code which when run will delete all rows in Worksheet A expect for rows which have dates in them and rows which have project names from Worksheet B.
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

ajetrumpet

Banned for being rude
Joined
Apr 12, 2008
Messages
569
Office Version
365, 2016, 2007
Platform
Windows
giggs, try this:

first, on worksheet A, add 3 columns to establish the criteria for deletion so it looks like this:

worksheet_A_formula_results.jpg


.....which is established by writing these formulas in the last 3 columns:

worksheet_A_formulas.jpg


and assuming your worksheet B looks like this:

worksheet_B_project_names.jpg


....then all you would need to do is run this code behind worksheet A:
Code:
Function del_rows_without_date_and_project()
Dim r As Range
Application.ScreenUpdating = False
    For Each r In Range("h2", Range("h2").End(xlDown))
        If r = "YES" Then 'DELETE THE ROW, OUR FORMULAS TELL US THE CRITERIA IS SET FOR THIS ROW
            r.EntireRow.Delete (xlUp)
        End If
    Next r
Application.ScreenUpdating = True
End Function
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,179
Messages
5,509,643
Members
408,746
Latest member
Faker4442

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top