Look in Column and Delete Certain Text

helpexcel

Well-known Member
Joined
Oct 21, 2009
Messages
656
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!
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
I want to delete/clear that cell

Which is it? Delete or Clear? and is "Grand Final" the only text in the cell?
 
Last edited:
Upvote 0
Try this:
Code:
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
 
Upvote 0
i guess .clearcontents, delete can get messy. "grand final" would be the only text in the cell.
 
Upvote 0
Did the code I posted do what you want?
 
Upvote 0
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.
 
Upvote 0
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?
 
Last edited:
Upvote 0
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.
 
Upvote 0
Did you put a . before any ranges?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,972
Members
448,537
Latest member
Et_Cetera

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