VBA delete rows which contains certain string

[Giggs1991]

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]

 

[gallen]

This does it, just ensure you change the 'fr' variable to suit

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

 

[Rick Rothstein]

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?

 

[DanteAmor]

Here another approach to delete rows.

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

 

[Giggs1991]

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.

 

[Giggs1991]

Thanks gallen, this code works.

 

[Rick Rothstein]

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?

 

[DanteAmor]

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:

Sub Macro1()
Range("A2:A" & Rows.Count).End(xlUp).SpecialCells(xlCellTypeConstants, 1).EntireRow.Delete
End Sub

 

[Giggs1991]

cells which do not have "project:" or "total" have dates and text in them. There are no numbers in column A.

 

[DanteAmor]

cells which do not have "project:" or "total" have dates and text in them. There are no numbers in column A.

ok, Then we discard the macro of post #8.If you can, I would like you to try the macro of post #4 .