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 :

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>
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
This does it, just ensure you change the 'fr' variable to suit
Code:
Sub DeleteRows()

'loop backwards as we are deleting
    Dim fr As Long, lr As Long 'first row and last row
    Dim l As Long  '- counter
    
    
    fr = 2 'First row:change as necessary
    
    lr = Range("A" & Rows.Count).End(xlUp).Row
    
    'loop backwards through all rows
    For l = lr To fr Step -1
        'check the cell doesn't contain the strings
        If InStr(1, Range("A" & l), "Project:") = 0 And InStr(1, Range("A" & l), "Total") = 0 Then
            'disable events as we are changing
            Application.EnableEvents = False
            'delete
            Rows(l).Delete
            're-enable events
            Application.EnableEvents = True
        End If
    Next l
    
End Sub
 
Upvote 0
I have a file which has the following information in Column A :

1
34
45
Total
Project: XYZ
3
56
Total
How representative is this list? Is Total and Project the only non-numeric text that could be in your cells?

Asked another way, are you trying to remove rows where the cells contain numbers only?

Also, in addition to that question, what is in your cells... constants or formulas?
 
Upvote 0
Here another approach to delete rows.

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)
  For i = 1 To UBound(a)
    If Not (a(i, 1) Like "*Project*" Or a(i, 1) Like "*Total*") Then Set r = Union(r, Range("A" & i + 1))
  Next i
  r.EntireRow.Delete
End Sub
 
Upvote 0
Hi Rick,
The file has over 500 rows with data.
Every row that does not contain the string " project:" and the string " Total" should be deleted. there are no formulas in the cells.
 
Upvote 0
Every row that does not contain the string " project:" and the string " Total" should be deleted.
I understand that, but my question was about the type of data you have. Your example showed that if the cell did not contain either of those words, then it had a number (not a text string) in it. My question was is this universally the case. If so, there is a relatively simple macro available, but I need to know if your example was truly representative. Are the only cells that do not contain either of those words in fact numerical?
 
Upvote 0
Hi Rick,
The file has over 500 rows with data.
Every row that does not contain the string " project:" and the string " Total" should be deleted. there are no formulas in the cells.


Hi Giggs1991, you tried the macro in post #4 .


Rick's question is to know if the cells that have numbers are the ones to be deleted, if so, you can try the following:

Code:
Sub Macro1()
Range("A2:A" & Rows.Count).End(xlUp).SpecialCells(xlCellTypeConstants, 1).EntireRow.Delete
End Sub
 
Upvote 0
cells which do not have "project:" or "total" have dates and text in them. There are no numbers in column A.
 
Upvote 0

Forum statistics

Threads
1,213,511
Messages
6,114,054
Members
448,543
Latest member
MartinLarkin

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