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

 

[daveyc18]

also sometimes column G has the text "rep," but beside in column GH it's blank, but i also want the macro to automatically fill in "rep" in column H if column G has "rep"

 

[JoeMo]

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

Change "=" to "Like" (w/o the quote marks).

 

[daveyc18]

Change "=" to "Like" (w/o the quote marks).

thanks !it worked!

how about this one:

also sometimes column G's rows has the text "rep," but beside in column H it's blank, but i also want the macro to automatically fill in "rep" in column H if column G has "rep"

 

[JoeMo]

thanks !it worked!

how about this one:

also sometimes column G's rows has the text "rep," but beside in column H it's blank, but i also want the macro to automatically fill in "rep" in column H if column G has "rep"

You are welcome.

In col G, is "rep" returned by a formula or is it a constant?

 

[daveyc18]

You are welcome.

In col G, is "rep" returned by a formula or is it a constant?

it's a constant

 

[JoeMo]

it's a constant

Try this:

Sub RepToColH()
Dim c As Range
With Range("G:G")
    .Replace "rep", "#N/A"
    For Each c In .SpecialCells(xlCellTypeConstants, xlErrors)
        c.Resize(1, 2).Value = "rep"
    Next c
End With
End Sub

 

[daveyc18]

says "no cells were found"

 

[JoeMo]

says "no cells were found"

On what line?

 

[daveyc18]

On what line?

Doesn't say....just in case i wasnt clear..not all rows in column G have "repo"...just some..and I want those rows eith repo to have "repo " in column H, too