Delete Specific Cell Content Based on Criteria

Barlew · Jul 9, 2011

Hello

I have about 1000 Spreadsheets that I’m working with, that all need their template updated. Using a combination of Macro Express and Excel VBA I’m nearly at a point where the update will be completely automated. The final piece of my jigsaw is as follows:

I need to delete some text from a specific cell. This cell might be in a slightly different reference in each of the 1000 sheets, BUT will always be adjacent to another cell containing unique text. SO what I need is VBA to:

Find the cell containing the text "Test description"
Look into the cell to the right of it
Find the characters " - " (that’s space dash space) in that cell
Then delete " - " and everything before it

There maybe more instances of " - " I’m only interested in the first " - " found.

Thanks

Cindy Ellis · Jul 9, 2011

The following macro should do what you've described:

Code:

Sub FixCellContent()
'
Dim Cell2Edit As String
    Cell2Edit = Cells.Find(What:="Test Description", LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Offset(0, 1).Address
    
    Range(Cell2Edit).Value = Mid(Range(Cell2Edit).Value, InStr(1, Range(Cell2Edit).Value, " - ") + 3)
    
End Sub

Hope that helps,
Cindy

Barlew · Jul 9, 2011

Thank you Cindy, that worked like a charm

Cindy Ellis · Jul 9, 2011

Thanks for the feedback, and best of luck with your project!

Barlew · Jul 9, 2011

Ah, just tested the code a bit further. I need the code not to run if the cell doesn't contain " - ". In it's current form it will still delete some text from the cell even if " - " has not been found.

sorry!

Cindy Ellis · Jul 9, 2011

Ah...I should have tested it better! Sorry about that.
This modification should solve the problem:

Code:

Sub FixCellContent()
'
Dim Cell2Edit As String
    Cell2Edit = Cells.Find(What:="Test Description", LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Offset(0, 1).Address
    If InStr(1, Range(Cell2Edit).Value, " - ") > 0 Then
        Range(Cell2Edit).Value = Mid(Range(Cell2Edit).Value, InStr(1, Range(Cell2Edit).Value, " - ") + 3)
    End If
End Sub

Cindy

Barlew · Jul 9, 2011

Nope, it was defiantly my fault, you’re the supplier, im the client! It’s my responsibility to test the solution fully and submit any change requests

Speaking of change requests. As i was testing i noticed that some of the spreadsheets contain one instance of " - " but don’t need anything deleting! So is it possible for the script to first search for "TS" in the cell adjacent to "Test Description" and if found carry on and run your code below?

Code:

Sub FixCellContent()
'
Dim Cell2Edit As String
    Cell2Edit = Cells.Find(What:="Test Description", LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Offset(0, 1).Address
    If InStr(1, Range(Cell2Edit).Value, " - ") > 0 Then
        Range(Cell2Edit).Value = Mid(Range(Cell2Edit).Value, InStr(1, Range(Cell2Edit).Value, " - ") + 3)
    End If
End Sub

Rick Rothstein · Jul 9, 2011

Here is a one-liner that should do what you asked for...

Code:

Cells.Find("Test description", , xlValues, xlWhole, , , False).Offset(, 1).Replace "* - ", "", xlPart

The above code line assumes the text "Test description" will always be located on the worksheet you run it against. If that will not always be the case then you will need to use something like this (although you may have to integrate it with any other error trapping you are using)...

Code:

On Error Resume Next
Cells.Find("Test description", , xlValues, xlWhole, , , False).Offset(, 1).Replace "* - ", "", xlPart
On Error GoTo 0

Cindy Ellis · Jul 9, 2011

If "TS" is in the same cell as the " - ", the following should work:

Code:

Sub FixCellContent()
'
Dim Cell2Edit As String
    Cell2Edit = Cells.Find(What:="Test Description", LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Offset(0, 1).Address
    If InStr(1, Range(Cell2Edit).Value, "TS") > 0 And InStr(1, Range(Cell2Edit).Value, " - ") > 0 Then
        Range(Cell2Edit).Value = Mid(Range(Cell2Edit).Value, InStr(1, Range(Cell2Edit).Value, " - ") + 3)
    End If
End Sub

If it's in the cell adjacent to the left of Test Description, try this:

Code:

Sub FixCellContent2()
'
Dim Cell2Edit As String
    Cell2Edit = Cells.Find(What:="Test Description", LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Offset(0, 1).Address
    If InStr(1, Range(Cell2Edit).Offset(0, -2).Value, "TS") Then
        If InStr(1, Range(Cell2Edit).Value, " - ") > 0 Then
            Range(Cell2Edit).Value = Mid(Range(Cell2Edit).Value, InStr(1, Range(Cell2Edit).Value, " - ") + 3)
        End If
    End If
End Sub

Both of these assume that there is only one instance of "Test Description" in the worksheet.
Hope that helps,
Cindy

Barlew · Jul 9, 2011

Thank you so much Cindy. This worked lovely. And thank you Rick for taking the time to reply.

Code:

Sub FixCellContent()
'
Dim Cell2Edit As String
    Cell2Edit = Cells.Find(What:="Test Description", LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Offset(0, 1).Address
    If InStr(1, Range(Cell2Edit).Value, "TS") > 0 And InStr(1, Range(Cell2Edit).Value, " - ") > 0 Then
        Range(Cell2Edit).Value = Mid(Range(Cell2Edit).Value, InStr(1, Range(Cell2Edit).Value, " - ") + 3)
    End If
End Sub

Delete Specific Cell Content Based on Criteria

Barlew

New Member

Excel Facts

Cindy Ellis

MrExcel MVP

Barlew

New Member

Cindy Ellis

MrExcel MVP

Barlew

New Member

Cindy Ellis

MrExcel MVP

Barlew

New Member

Rick Rothstein

MrExcel MVP

Cindy Ellis

MrExcel MVP

Barlew

New Member

Similar threads

Forum statistics

Share this page

Delete Specific Cell Content Based on Criteria

New Member

Excel Facts

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

MrExcel MVP

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock