Skip to content

Instantly share code, notes, and snippets.

@angelyordanov

angelyordanov/concatenate.vb

Last active January 21, 2020 10:44
Show Gist options
  • Save angelyordanov/bbe117bf8466ac2045320d0620456798 to your computer and use it in GitHub Desktop.
Save angelyordanov/bbe117bf8466ac2045320d0620456798 to your computer and use it in GitHub Desktop.
Download ZIP
Concatenate a range selection(number of rows) into the top rows' cells
Raw
readme.md
  1. Show the Developer Tab in Excel
  2. Create a AddIn (xlam) workbook
    1. Open a new workbook
    2. Click on the Visual Basic button in the Developer Tab
    3. Right click in the Project pane and "Insert" > "Module"
    4. Paste the concatenate.vb contents in the new window
    5. Close the VisualBasic editor and save the workbook as a Excel Add-in (.xlam) Note that this file is global and should be present every time you open excel. If you delete it you will start getting a warning before you go to Tool>AddIns and remove it.
  3. Add the xlam file as addin in Tools > "Execel Add-ins"
  4. Add the macro to the Quick Access Toolbar
  5. Use it by clicking on the macro button and making a selection
Raw
concatenate.vb
Option Explicit
Sub Concatenate()
Dim rSelected As Range
Dim col As Range
Dim c As Range
Dim first As Boolean
'Prompt user to select cells for formula
On Error Resume Next
Set rSelected = Application.InputBox(Prompt:= _
"Select cells to create formula", _
Title:="", Type:=8)
On Error GoTo 0
Dim newLine As String
#If Win32 Or Win64 Then
newLine = Chr(10)
#ElseIf Mac Then
newLine = vbNewLine
#End If
'Only run if cells were selected and cancel button was not pressed
If Not rSelected Is Nothing Then
For Each col In rSelected.Columns
first = True
For Each c In col.Cells
If first = False Then
col.Cells(1).Value = col.Cells(1).Value & newLine & c.Value
c.Value = ""
End If
first = False
Next c
Next col
End If
End Sub
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment