Delete string line (row) based on newline

[Brutusar]

Hi, I have a situation where there may be 1-5 lines of text in the same cell, and I have to delete one or several of them. For instance:

Miami
New York
Los Angeles
Atlanta
San Francisco

I may need to remove for example the top 4, or only "Atlanta" or any other line. I can hardcode which line(s) to remove.

How do I do that best?

 

[Marius44]

HI
Assuming that the cities are written in cell A1 and that the city to be deleted is written in D1, try this macro

Option Explicit

Sub DelCity()
Dim cities As String, todel As String
Dim ltot As Integer, lcity As Integer, car As Integer
Dim sx As String, dx As String
cities = Cells(1, 1).Text
todel = Cells(1, 5).Text
ltot = Len(cities)
lcity = Len(todel)
car = Application.WorksheetFunction.Search(todel, cities)
Cells(2, 1) = Left(cities, car - 1) & Right(cities, ltot - car - lcity)
End Sub

If the cities to be deleted are more than one then they must be inserted with Alt+Enter
Let know. HI,
Mario

 

[Brutusar]

HI
Assuming that the cities are written in cell A1 and that the city to be deleted is written in D1, try this macro

Option Explicit

Sub DelCity()
Dim cities As String, todel As String
Dim ltot As Integer, lcity As Integer, car As Integer
Dim sx As String, dx As String
cities = Cells(1, 1).Text
todel = Cells(1, 5).Text
ltot = Len(cities)
lcity = Len(todel)
car = Application.WorksheetFunction.Search(todel, cities)
Cells(2, 1) = Left(cities, car - 1) & Right(cities, ltot - car - lcity)
End Sub

If the cities to be deleted are more than one then they must be inserted with Alt+Enter
Let know. HI,
Mario

Hi, thanks for the code.

When I have the cities in A1 and running the code, it deletes the first letter in the first city, and copies everything else to A2.

 

[Marius44]

Hi
What you have written in D1? How are written the cities in A1?

Hi,
Mario

 

[JGordon11]

the following code works on the selected cell. It takes input in the form of which number lines to delete

example entering 2,3,5-8 would delete lines 2,3,5,6,7,8

Sub DeleteLinesInSelectedCell()
    Dim i As Long, j As Long, lines As String, a, b, c, txt As String, ans As Long, t As String, changes As Boolean
    If Len(Selection(1, 1)) = 0 Then
        MsgBox "Nothing selected"
        Exit Sub
    End If
    a = Split(Selection(1, 1), Chr(10))
    For i = 0 To UBound(a)
        t = t & i + 1 & ".  " & a(i) & Chr(10)
    Next
    t = Left(t, Len(t) - 1)
    lines = InputBox(t, "Which lines to delete?")
    If lines = "" Then Exit Sub
    lines = Replace(lines, " ", "")
    b = Split(lines, ",")
    For i = 0 To UBound(b)
        If InStr(1, b(i), "-") > 0 Then
            t = ""
            c = Split(b(i), "-")
            For j = c(0) To c(1)
                t = t & j & ","
            Next
            t = Left(t, Len(t) - 1)
            b(i) = t
        End If
    Next
    lines = "," & Join(b, ",") & ","
    For i = 0 To UBound(a)
        If InStr(1, lines, "," & i + 1 & ",") > 0 Then
            a(i) = ""
            changes = True
        End If
    Next
    If changes Then
        txt = Application.TextJoin(Chr(10), 1, a)
        ans = MsgBox("Confirm: change cell " & Selection.Address(0, 0) & " to " & Chr(10) & Chr(10) & txt, vbYesNo)
        If ans = vbYes Then Selection(1, 1) = txt
    Else
        MsgBox "No changes selected"
    End If
End Sub