+ Reply to Thread
Results 1 to 4 of 4

Need help with a macro that combines cells

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    05-12-2021
    Location
    USA
    MS-Off Ver
    Microsoft Office 365
    Posts
    259

    Need help with a macro that combines cells

    Hi, I found a macro that will combine cells that I highlight but separate them with a space, two || symbols, and another space. The cells being combined are simply a set of numbers in a 4 digit format. So for example, if I will highlight cells J2:J4 which contain 3 different numbers and then run the macro while those are selected, the result populates in K2 and it will look like: 0943 || 0026 || 0075 ||.
    The code I found and used is:

    Sub combineLMnumbers()
     Dim rng As Range
     Dim i As String
     For Each rng In Selection
     i = i & rng & " || "
     Next rng
     Range("K2").Value = Trim(i)
     End Sub
    This is great, but I am looking for a slight alteration for it to not have that space and symbols after the last number so that it would instead read 0943 || 0026 || 0075

    If it helps any, in the code where I have " || ", that originally was just " " and I changed to to the " || " as that suits the purpose I need it for. Is there a simple adjustment that can be made to the above code to allow for the result I am looking for where it doesn't put the || symbols after the last number?
    Last edited by PitchNinja; 04-11-2022 at 03:24 PM.

  2. #2
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    8,033

    Re: Need help with a macro that combines cells

    One simple adjustment as requested
    Sub combineLMnumbers()
        Dim rng As Range
        Dim i As String
        For Each rng In Selection
            i = i & rng & " || "
        Next rng
        Range("K2").Value = Trim(Left(i, Len(i) - 3))
    End Sub
    BSB

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    49,069

    Re: Need help with a macro that combines cells

    @BSB: wouldn't you need to reduce the length by 4?

    Edit: oh, never mind, it's Trimmed anyway. Dohhh.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Forum Contributor
    Join Date
    05-12-2021
    Location
    USA
    MS-Off Ver
    Microsoft Office 365
    Posts
    259

    Re: Need help with a macro that combines cells

    Awesome! Thanks a bunch for that - working exactly how I want it to now

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. MACRO that insert text files and combines them
    By Fallengreg in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-11-2020, 09:56 AM
  2. Need help with Macro that filters, combines, then saves as different workbooks
    By jthearne in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-30-2019, 02:26 PM
  3. [SOLVED] Macro that combines data from several workbooks in a folder.
    By taylorsm in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-18-2017, 12:26 PM
  4. [SOLVED] Macro combines cells into a string, but puts undesired comma at the beginning.
    By zookeepertx in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-21-2014, 12:57 PM
  5. [SOLVED] A macro that combines text data in different cells into one cell?
    By kjy1989 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-05-2013, 02:21 AM
  6. Macro that combines if statements to code cells based on different criteria?
    By tomqueens in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-24-2012, 12:12 PM
  7. A loop that joins combines two cells
    By Josh_123456 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-21-2007, 05:45 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1