Look in Column and Delete Certain Text

helpexcel

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

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,858
Office Version
365, 2010
Platform
Windows, Mobile
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:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,643
Office Version
365
Platform
Windows
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
 

helpexcel

Well-known Member
Joined
Oct 21, 2009
Messages
553
i guess .clearcontents, delete can get messy. "grand final" would be the only text in the cell.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,643
Office Version
365
Platform
Windows
Did the code I posted do what you want?
 

helpexcel

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,486
Office Version
365
Platform
Windows
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:

helpexcel

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,486
Office Version
365
Platform
Windows
Did you put a . before any ranges?
 

helpexcel

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

Watch MrExcel Video

Forum statistics

Threads
1,102,043
Messages
5,484,368
Members
407,437
Latest member
alfaroM

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top