Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: VBA Code to delete information following a character

  1. #1
    Board Regular
    Join Date
    Apr 2013
    Posts
    104
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA Code to delete information following a character

    I have a report where every cell in column "F" has an expense description separated by multiple colon ":" symbols. I cannot seem to write a macro that will find the last colon in the cell and eliminate everything following it, including the last colon as well.

    I would like the program to do this for every cell in the entire column as I dont know how long the report will be and there are blanks in between cells at times.

    Here is the format in the cells:

    Cell Phone:Cell Phone - October:Verizon

    thank you so much and let me know if you have questions.

  2. #2
    Board Regular Paul Ked's Avatar
    Join Date
    Jun 2015
    Location
    LLangollen, Wales
    Posts
    130
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code to delete information following a character

    try:
    Code:
    Sub Clean()
        Dim lr As Long, x As Long
        lr = Cells(Rows.Count, 6).End(xlUp).Row
        For x = 1 To lr
            If Cells(x, 6) = "" Then GoTo Skip
            Cells(x, 7) = Left(Cells(x, 6), InStrRev(Cells(x, 6), ":", -1) - 1)
    Skip:
        Next
    End Sub
    Best regards

    Paul Ked

  3. #3
    Board Regular
    Join Date
    Apr 2013
    Posts
    104
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code to delete information following a character

    I get a run time error '5'

    Invalid procedure call or argument in the last line Cells(x, 7)....

    any advice?

  4. #4
    Board Regular Paul Ked's Avatar
    Join Date
    Jun 2015
    Location
    LLangollen, Wales
    Posts
    130
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code to delete information following a character

    What version of Excel are you using?

  5. #5
    Board Regular
    Join Date
    Apr 2013
    Posts
    104
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code to delete information following a character

    2013

  6. #6
    Board Regular Paul Ked's Avatar
    Join Date
    Jun 2015
    Location
    LLangollen, Wales
    Posts
    130
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code to delete information following a character

    It works fine for me.

    https://www.dropbox.com/s/29jn9ia0gd...olon.xlsm?dl=0

    That should be a link to the file.

  7. #7
    Board Regular Paul Ked's Avatar
    Join Date
    Jun 2015
    Location
    LLangollen, Wales
    Posts
    130
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code to delete information following a character

    The error is created when one of the items in the list does not contain a ":".

  8. #8
    Board Regular Paul Ked's Avatar
    Join Date
    Jun 2015
    Location
    LLangollen, Wales
    Posts
    130
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code to delete information following a character

    Code:
    Sub Clean()
        Dim lr As Long, x As Long
        lr = Cells(Rows.Count, 6).End(xlUp).Row
        For x = 1 To lr
            If Cells(x, 6) = "" Then GoTo Skip
            If InStr(1, Cells(x, 6), ":") = 0 Then GoTo Skip
            Cells(x, 7) = Left(Cells(x, 6), InStrRev(Cells(x, 6), ":", -1) - 1)
    Skip:
        Next
    End Sub
    Should work!

  9. #9
    Board Regular
    Join Date
    Apr 2013
    Posts
    104
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code to delete information following a character

    thanks.

    I am not getting any errors but when i run nothing happens. does the sheet name need to be selected or something? the cell selected is A1 and when i run, A1 is still selected.

  10. #10
    Board Regular Paul Ked's Avatar
    Join Date
    Jun 2015
    Location
    LLangollen, Wales
    Posts
    130
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code to delete information following a character

    The list should be in column F "Cells(x, 6)", as per your original post, and the 'corrected' data in column G "Cells(x, 7)" after the sub has been run.

    The correct sheet must be active as I did not put any sheet reference (eg Sheet1.Cells(x, 6) or Sheets("Data").Cells(x, 6)) in the sub.

    You don't need to select anything.
    Last edited by Paul Ked; Nov 29th, 2017 at 03:06 PM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •