Look in Column and Delete Certain Text

[helpexcel]

Hi - I want to look in Column A and if the text "Grand Final" is found, I want to delete/clear that cell, and then sort alphabetically. Thanks!

 

[MARK858]

I want to delete/clear that cell

Which is it? Delete or Clear? and is "Grand Final" the only text in the cell?

 

[Joe4]

Try this:

Sub MySort()

'   Replace all values of "Grand Final" in column with nothing
    On Error Resume Next
    Columns("A:A").Replace What:="Grand Final", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    On Error GoTo 0
        
'   Sort by column A
    Columns("A:A").Sort key1:=Range("A1"), order1:=xlAscending, Header:=xlNo

End Sub

 

[helpexcel]

i guess .clearcontents, delete can get messy. "grand final" would be the only text in the cell.

 

[Joe4]

Did the code I posted do what you want?

 

[helpexcel]

it works when i run it by itself, but when I incorporate it into a large code it errors out. I added a With statement to make sure it runs on the correct sheet, but i still get a Run-Time error '1004': This operation requires the merged cells to be identically sized. There are no merged cells on the sheet.

 

[Fluff]

There are no merged cells on the sheet.

I suspect that you are wrong & that there are merged cells, hence the error message.
Either that or you are not looking at the correct sheet.
What is your code?

 

[helpexcel]

I just added Sheet1.Activate to the top of the code and it's working. I'm not sure why it wouldn't work when I bracketed the code in With Sheet1/End With.

 

[Fluff]

Did you put a . before any ranges?

 

[helpexcel]

I didn't. So I used this, and now i get a sort error.



Sub MySort()

With Sheet1
' Replace all values of "Grand Final" in column with nothing
On Error Resume Next
.Columns("A:A").Replace What:="Grand Final", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
On Error GoTo 0

' Sort by column A
.Columns("A:A").Sort key1:=Range("A1"), order1:=xlAscending, Header:=xlYes
End With

End Sub