VBA delete rows which contains certain string

Giggs1991

Board Regular
Joined
Mar 17, 2019
Messages
50
Hi,
I have a file which has the following information in Column A :

[TABLE="width: 348"]
<tbody>[TR]
[TD="class: xl63, width: 348"]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
"




[/TD]
[/TR]
</tbody>[/TABLE]
 
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
 
Upvote 0

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Last edited:
Upvote 0
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.


 
Upvote 0
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...
Code:
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,908
Messages
6,175,306
Members
452,633
Latest member
DougMo

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