VBA delete rows which contains certain string

[Giggs1991]

Hi,
I have a file which has the following information in Column A :

Project: ABC 1 34 45 Total Project: XYZ 3 56 Total I would like to all rows which do not contain "Project:" or "Total" to be deleted IN this case, the final output should look like this : Project :ABC Total Poject:XYZ Total. I was looking for VBA code to do this. I tried something like the following but did not work exactly as I wanted : " For i = 1 To 500 ' Revise the 500 to include all of your values If InStr(Cells(i, 1).Value, "Project:") = False Then ws.Rows(i).EntireRow.Delete End If Next i "

<tbody>
</tbody>

 

[Giggs1991]

Thanks Dante. As mentioned in post 9, cells which do not have "project:" or "total" have dates and text in them. Is there an line I can add to the vba code to retain the dates are well . This means the vba code will retain rows which have "project", "TOTAL" and dates and will delete every other row. Dates are in following format : 1/07/2019

 

[DanteAmor]

Thanks Dante. As mentioned in post 9, cells which do not have "project:" or "total" have dates and text in them. Is there an line I can add to the vba code to retain the dates are well . This means the vba code will retain rows which have "project", "TOTAL" and dates and will delete every other row. Dates are in following format : 1/07/2019

Please, you can give a more specific example with representative data.

 

[Giggs1991]

Column A would look like this :
Project: ABC
1
15/07/2019
34
45
Total
Project: XYZ
3
56
Total
18/07/2019

After running the vba code, only the rows which have "project" or "TOTAL" or a date in column A should remain. Every other row should get deleted.

 

[Rick Rothstein]

Column A would look like this :
Project: ABC
1
15/07/2019
34
45
Total
Project: XYZ
3
56
Total
18/07/2019

After running the vba code, only the rows which have "project" or "TOTAL" or a date in column A should remain. Every other row should get deleted.

In Message #9 , you said cells without "Project" or "Total" would have dates and text, but your example is showing numbers and dates. Please clarify.


Also consider the following generalized comment I have posted in the past...

Please Note
-------------------
For future questions you may ask, please do not simplify your question for us... doing so will get you a great answer to a question you do not actually have and which you do not actually care about AND it will almost always lead to you coming back for help when the solution we give you for the simplified question cannot be applied to your actual data and its layout. One thing you must keep in mind when you ask a question in a forum... the people you are asking to help you know absolutely nothing about your data, absolutely nothing about how it is laid out in the workbook, absolutely nothing about what you want done with it and absolutely nothing about how whatever it is you want done is to be presented back to you as a result... you must be very specific about describing each of these areas, in detail, and you should not assume that we will be able to "figure it out" on our own. Remember, you are asking us for help... so help us to be able to help you by providing the information we need to do so, even if that information seems "obvious" to you (remember, it is only obvious to you because of your

 

[Giggs1991]

let me rephrase please : column A will look like this :

Project: ABC
1
15/07/2019
34
45
Total
Project: XYZ
3
XYZ
56
ABC
Total
18/07/2019

after running the vba code, only the rows which have "project" or "TOTAL" or a date in column A should remain. Every other row should get deleted.

 

[Rick Rothstein]

let me rephrase please : column A will look like this :

Project: ABC
1
15/07/2019
34
45
Total
Project: XYZ
3
XYZ
56
ABC
Total
18/07/2019

after running the vba code, only the rows which have "project" or "TOTAL" or a date in column A should remain. Every other row should get deleted.

Here is Dante's code from Message #4 modified (shown in red) to keep dates also...

Sub Delete_Rows()
  Dim lr As Long, i As Long, a As Variant, r As Range
  lr = Range("A" & Rows.Count).End(xlUp).Row
  Set r = Range("A" & lr + 1)  '
  a = Range("A2:A" & lr).Value
  For i = 1 To UBound(a)
    If Not (a(i, 1) Like "*Project*" Or a(i, 1) Like "*Total*" [B][COLOR="#FF0000"]Or IsDate(a(i, 1))[/COLOR][/B]) Then Set r = Union(r, Range("A" & i + 1))
  Next i
  r.EntireRow.Delete
End Sub

Note: Dates can be hard to verify in Excel due to the way they are stored. VBA's IsDate function is not perfect and will allow some strange values to be considered as dates; however, it would appear that your values are kind of normal so I don't think they will be affected by IsDate's anomalies.

 

[Giggs1991]

Thank you very much Rick and Dante. this code works perfectly for me.

 

[DanteAmor]

Thank you very much Rick and Dante. this code works perfectly for me.

I'm glad to help you. Thanks for the feedback.

Thanks Rick for the complement.