delete rows with certain text

[daveyc18]

hi,

after formatting,

I have some rows that contain "page xx of xx" and I want those lines deleted. i tried the following code , but to no avail:

Dim count
For count = Range("A" & Rows.count).End(xlUp).Row To 1 Step -1
If Range("A" & count) = "page*" Then Rows(count).Delete
Next

 

[JoeMo]

Doesn't say....just in case i wasnt clear..not all rows in G have "repo"...just some

You very definitely were not clear. Your post said you were looking for "rep" NOT "repo" and there's all the difference in the world between the two. Change "rep" to "repo" in the code I gave you and try again.

 

[daveyc18]

You very definitely were not clear. Your post said you were looking for "rep" NOT "repo" and there's all the difference in the world between the two. Change "rep" to "repo" in the code I gave you and try again.

my bad...also trying same code with "reverse repo"

  Dim [B]d [/B]As Range
With Range("f:f")
    .Replace "REVERSE REPO", "#N/A"
    For Each c In .SpecialCells(xlCellTypeConstants, xlErrors)
        [B]d[/B].Resize(1, 2).Value = "REVERSE REPO"
    Next [B]d[/B]
End With

but saying "no cells founds"

 

[JoeMo]

my bad...also trying same code with "reverse repo"

  Dim [B]d [/B]As Range
With Range("f:f")
    .Replace "REVERSE REPO", "#N/A"
    For Each c In .SpecialCells(xlCellTypeConstants, xlErrors)
        [B]d[/B].Resize(1, 2).Value = "REVERSE REPO"
    Next [B]d[/B]
End With

but saying "no cells founds"

That's not the code I gave you. Why would you change c to d in some places but not all????????? And you were searching col G before, now it's col F - is that correct?

 

[daveyc18]

That's not the code I gave you. Why would you change c to d in some places but not all????????? And you were searching col G before, now it's col F - is that correct?

sorry i was in a hurry.... column F is now the correct column

so, yah, your original code works perfectly...any "repo" in column F populates the blanks in column G with "repo," too

now in column F there are lines with "reverse repo" as well... need column G to say "reverse repo," too if it's blank

 

[JoeMo]

sorry i was in a hurry.... column F is now the correct column

so, yah, your original code works perfectly...any "repo" in column F populates the blanks in column G with "repo," too

now in column F there are lines with "reverse repo" as well... need column G to say "reverse repo," too if it's blank

I don't recall the bit about the G cell being blank in any of your prior posts.

You need to repeat the With - End With block for each search term and adjust for non-blank G cells that are companions to F cells with the search term so they are not overwritten with the search term.

 

[daveyc18]

I don't recall the bit about the G cell being blank in any of your prior posts.

You need to repeat the With - End With block for each search term and adjust for non-blank G cells that are companions to F cells with the search term so they are not overwritten with the search term.

well, column H was originally blank, but now the source file has shifted over so it's now column G.

your original solution worked perfectly, but cant seem to repeat the process with "Reverse repo" ...anyway, ill keep trying

 

[JoeMo]

well, column H was originally blank, but now the source file has shifted over so it's now column G.

your original solution worked perfectly, but cant seem to repeat the process with "Reverse repo" ...anyway, ill keep trying

Try this, but please try it before you start to modify it or change the layout it expects to encounter.

Sub RepToColG()
Dim c As Range, i As Long, S As Variant
S = Array("Reverse Repo", "repo")
For i = LBound(S) To UBound(S)
    With Range("F:F")
        .Replace S(i), "#N/A", xlWhole
        On Error Resume Next
        For Each c In .SpecialCells(xlCellTypeConstants, xlErrors)
            On Error GoTo 0
            If Not c Is Nothing Then
                If c.Offset(0, 1) = "" Then
                    c.Resize(1, 2).Value = S(i)
                Else
                    c.Value = S(i)
                End If
            Else
                MsgBox "no cells with " & S(i) & " in col F"
                GoTo Nx
            End If
        Next c
    End With
Nx:
Next i
End Sub

 

[daveyc18]

Try this, but please try it before you start to modify it or change the layout it expects to encounter.

Sub RepToColG()
Dim c As Range, i As Long, S As Variant
S = Array("Reverse Repo", "repo")
For i = LBound(S) To UBound(S)
    With Range("F:F")
        .Replace S(i), "#N/A", xlWhole
        On Error Resume Next
        For Each c In .SpecialCells(xlCellTypeConstants, xlErrors)
            On Error GoTo 0
            If Not c Is Nothing Then
                If c.Offset(0, 1) = "" Then
                    c.Resize(1, 2).Value = S(i)
                Else
                    c.Value = S(i)
                End If
            Else
                MsgBox "no cells with " & S(i) & " in col F"
                GoTo Nx
            End If
        Next c
    End With
Nx:
Next i
End Sub

LOL you're a genius...actually, now my colleague doesn't want "reverse repo" --> "reverse repo" in the next column

column F column G
"repo" --> "repo"
"reverse repo" --> "repo"

 

[JoeMo]

LOL you're a genius...actually, now my colleague doesn't want "reverse repo" --> "reverse repo" in the next column

column F column G
"repo" --> "repo"
"reverse repo" --> "repo"

Then use this version:

Sub RepToColG()
Dim c As Range, i As Long, S As Variant
S = Array("Reverse Repo", "repo")
Application.ScreenUpdating = False
For i = LBound(S) To UBound(S)
    With Range("F:F")
        .Replace S(i), "#N/A", xlWhole
        On Error Resume Next
        For Each c In .SpecialCells(xlCellTypeConstants, xlErrors)
            On Error GoTo 0
            If Not c Is Nothing Then
                If c.Offset(0, 1) = "" Then
                    c.Resize(1, 2).Value = S(i)
                Else
                    c.Value = S(i)
                End If
            Else
                MsgBox "no cells with " & S(i) & " in col F"
                GoTo Nx
            End If
        Next c
    End With
Nx:
Next i
With Range("G:G")
    .Replace S(0), S(1), xlWhole
End With
Application.ScreenUpdating = False
End Sub