How to concatenate cells in a row until the first blank cell

I’m currently diving into code/VBA coding for the first time. I have a file that I dump into a worksheet that currently I’m manually organizing and pushing out. When put into the worksheet, it delimits itself across the cells. The first 2-4 cells are always parts of a name. This dump file will have varying row and column lengths every time I get it in a given day and dump into a work sheet. For example, one day it may be twenty rows and one day it may be thirty.

This is a rough illustration of what the data looks like, but my code probably doesn’t match with the example below – I just wanted to provide a visual:

  • Using Microsoft.Office.Interop.Excel without actually having Excel?
  • Create WPF dialog linked to specific workbook in Excel programmatically
  • Non-web SQL Injection
  • VBA error 1004 select method of worksheet class field
  • Randomize Columns
  • How can I send a range in the body of an email using a macro (mac)? (VBA)
  • screenshot of the spreadsheet, showing current vs desired result

    So, I’m wanting to make code that will start at A1 and concatenate the cells following it until it runs into a blank cell in that row. Then it places the concatenated data into cell A1 and removes the values it pulled the name pieces from and slides all the data to the left. After that, it continues the same operation on the next row until it meets the final row. As you can see in the image, I don’t want any of the data after the blank cell to be affected.

    This is my first time programming in general, so when you provide assistance, would you please explain your code so I can learn the concepts? Here’s what I think will work so far… I’m just stuck on how to go about concatenating.

    The code I currently have:

      Dim row As Integer
      NumRows = Range("A1", Range("A1").End(xldown)).Rows.Count
      For row = 1 To NumRows
          Do Until IsEmpty(ActiveCell)
              ' Code to concatenate
              ActiveCell.Offset(1, 0).Select
          ActiveCell.Offset(1, 0).Select
    End sub

  • Count Date Values of Current Month - MS Excel
  • Group the data points rows into coloumns
  • Excel Solver linearity conditions
  • Error 400 in Excel VBA, just got upgraded to 2016 from 2010
  • Extract Mail properties shown as blank on military classified SIPRNet
  • Parse a Json(with array and objects) and export the data into Excel file in Node.js
  • 2 Solutions collect form web for “How to concatenate cells in a row until the first blank cell”

    Here’s another way to look at your problem: Suppose you have your table on Sheet2, and the result is reflected on Sheet1.

    Sub PutInOrder()
      filledcells = 0
      '''lastrow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Row
      For i = 1 To 100
         If Sheet2.Cells(i, 1) = "" Then Exit For 
         For a = 1 To 4
            If Sheet2.Cells(i, a) = "" Then Exit For
              If Sheet2.Cells(i, a) <> "" Then
                 filledcells = filledcells + 1
            End If
    Select Case filledcells
        Case Is = 2
            Sheet1.Cells(i, 1) = Sheet2.Cells(i, 1) + ", " + Sheet2.Cells(i, 2)
            Sheet1.Cells(i, 3) = Sheet2.Cells(i, 4)
            Sheet1.Cells(i, 4) = Sheet2.Cells(i, 5)
            Sheet1.Cells(i, 5) = Sheet2.Cells(i, 6)
            Sheet1.Cells(i, 6) = Sheet2.Cells(i, 7)
        Case Is = 3
            Sheet1.Cells(i, 1) = Sheet2.Cells(i, 1) + ", " + Sheet2.Cells(i, 2) + " " + Sheet2.Cells(i, 3)
            Sheet1.Cells(i, 3) = Sheet2.Cells(i, 5)
            Sheet1.Cells(i, 4) = Sheet2.Cells(i, 6)
            Sheet1.Cells(i, 5) = Sheet2.Cells(i, 7)
            Sheet1.Cells(i, 6) = Sheet2.Cells(i, 8)
        Case Is = 4
            Sheet1.Cells(i, 1) = Sheet2.Cells(i, 1) + ", " + Sheet2.Cells(i, 2) + " " + Sheet2.Cells(i, 3) + " " + Sheet2.Cells(i, 4)
            Sheet1.Cells(i, 3) = Sheet2.Cells(i, 6)
            Sheet1.Cells(i, 4) = Sheet2.Cells(i, 7)
            Sheet1.Cells(i, 5) = Sheet2.Cells(i, 8)
            Sheet1.Cells(i, 6) = Sheet2.Cells(i, 9)
       End Select
       filledcells = 0
     End Sub

    Can you try this and let me know how you get on? It may need some tweaks depending on your precise layout. My approach is slightly different.

     Sub x()
    Dim n As Long, r1 As Range, r2 As Range, v
    For n = 1 To Range("A" & Rows.Count).End(xlUp).Row
        On Error Resume Next
        Set r1 = Cells(n, 1).EntireRow.SpecialCells(xlCellTypeConstants).Areas(1)
        Set r2 = Cells(n, 1).EntireRow.SpecialCells(xlCellTypeConstants).Areas(2)
        If Not r1 Is Nothing And Not r2 Is Nothing Then
            v = Join(Application.Transpose(Application.Transpose(r1)), ", ")
            Cells(n, 1) = WorksheetFunction.Proper(v)
            Cells(n, 2).Resize(, r1.Count).Clear
            r2.Cut Cells(n, 3)
        End If
    Next n
    End Sub
    MS Excel Spreadsheet is the best Office Software, Excel VBA and Excel Formulas make Spreadsheet work faster.