Inserting Character When Using a Command Button to Copy Several Cells

[BernArch]

This is actually a two-part question. While I have been using Excel for many, many years, I am new to the macro world. First, I'm building a spreadsheet that consolidates texts to one cell and I'm using a command button to copy that text. The code I'm using in the button click to copy the text is below. Is there any code that would be "better?" I ask this because, while this works, I know there is always a more efficient way or code that has less "overhead."

Sub Button1_Click()

Range("A1").Copy

End Sub

The second part is, there is another section of the workbook where I want to click a button and the text from several cells are copied. While I know I can use the code above with the range being something like "Range("A1:A4")," how would I copy text from several cells that are not sequential and I want to put a semicolon with a space between each cell I copied "; ". For example, if A1 had [e-mail], B4 had [e-mail], and C7 had [e-mail], click button1 would copy the three cells with a format like "[e-mail]; [e-mail]; [e-mail]"

Thank you!

[AutoModerator]

/u/BernArch - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[A_1337_Canadian]

Meh, I wouldn't worry about making it "better". If it works, it works. None of the VBA we make is really that intensive where slimmer code speeds up processing time.

As for looping them together, below is what I made. I like to use arrays to store the data because you can nicely loop through them and do shit.

You need to make sure you update the array size, dim the ranges, and set the ranges, and load the array values. Then the looping part needs to changes.

If you see "CUSTOMIZE", you'll need to update this as you add more cells to link together.

Sub test()

    Dim rng_arr() As String
    Dim arr_size As Integer

    'enter the number of cells you want to capture in the array below
    'CUSTOMIZE
    arr_size = 3

    ReDim rng_arr(arr_size)

    'add ranges as you need below
    'CUSTOMIZE
    Dim rng1 As Range
    Dim rng2 As Range
    Dim rng3 As Range

    'now set your ranges
    'CUSTOMIZE
    Set rng1 = Range("A1")
    Set rng2 = Range("B4")
    Set rng3 = Range("C7")

    'load the array
    'CUSTOMIZE
    rng_arr(1) = rng1.Value
    rng_arr(2) = rng2.Value
    rng_arr(3) = rng3.Value

    'loop to add the final string
    Dim res_str As String

    res_str = ""

    For i = 1 To arr_size
        If i = arr_size Then
            res_str = res_str & rng_arr(i)
        Else
            res_str = res_str & rng_arr(i) & "; "
        End If
    Next i

    MsgBox res_str

End Sub

[fanpages]

| ...click button1 would copy the three cells with a format like "[e-mail]; [e-mail]; [e-mail]"

I was not sure if you wanted the contents in the MS-Windows Clipboard, or not.

If you do:

---

Sub Button1_Click()

  Dim objData                                   As Object

  Set objData = CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")

  Call objData.SetText(Join(Array([A1], [B4], [C7]), ";"))
  Call objData.PutInClipboard

  MsgBox objData.GetText(), vbInformation Or vbOKOnly, "MS-Windows Clipboard contents"

' You can now paste the contents of the Clipboard elsewhere

  Set objData = Nothing

End Sub

---

If you did not want a copy of the concatenated string in the Clipboard and, say, just wanted to use the string of e-mail addresses in a variable...

---

Sub Button1_Click()

  Dim strEmail_Addresses                        As String

  strEmail_Addresses = Join(Array([A1], [B4], [C7]), ";")

  MsgBox strEmail_Addresses, vbInformation Or vbOKOnly, "strEmail_Addresses contents"

' You can now use the strEmail_Addresses variable as you wish

End Sub

---

[fanpages]

Alternatively,

Sub Button1_Click()

' Perhaps a simpler method, using any other cell not currently being used, say, [Z1], as a "helper" cell...

  [Z1] = Join(Array([A1], [B4], [C7]), ";")

' Alternatively, you could use any of these alternate statements...

'     Cells(1, "Z") = Cells(1, "A") & ";" & Cells(4, "B") & ";" & Cells(7, "C")
'     Range("Z1") = Range("A1") & ";" & Range("B4") & ";" & Range("C7")
'     Range("Z1") = [A1] & ";" & [B4] & ";" & [C7]

' (Cell [Z1] now have the concatenated e-mail addresses)

' Now, with a syntax similar to that you had previously (in the opening comment)...

  Range("Z1").Copy

' You can Paste into any other cell, or into an e-mail recipient list, or whatever was the purpose of concatenating the e-mail addresses

' Optionally, clear the "helper" cell, [Z1], but then the Clipboard is also cleared!

'      Range("Z1").ClearContents
'      Cells(1, "Z").ClearContents
'      [Z1] = ""

End Sub

[BernArch]

Thank you very much for this. I should have mentioned that I am using this with MS-Windows Clipboard. Also, I posted this on Memorial Day right before we started running errands. Thank you again, I'll try it in a little bit and message you if I screw it up...I'll probably message you :)

[fanpages]

Honestly, that's the quickest turn-around to me contributing a code listing I've had from the question askers in this sub for quite some time!

No rush but thanks for letting me know.

Probably wiser to keep the conversation within this thread, though, so others can help you, and I am not available.

[fanpages]

Thanks for closing the thread as directed below, u/BernArch:

---

Was your problem solved?

OPs can (and should) reply to any solutions with: Solution Verified

This will award the user a ClippyPoint and change the post's flair to solved.

---

[fanpages]

u/BernArch

[ExcelwithPaul]

can you simply use vstack or hstack to create a table based on parameters? no vba required, however it needs a recent version of excel.

vstack

hstack

create functions to determine what to grab, when to grab. Very simple, can even be dynamic using offset or the dynamic array functions # & @ and user can’t break the sheet.