VBA concatenate

[sethae]

I'm trying to use a concatenate function in a macro across several sheets. The problem I'm running into is that the number of columns in each sheet is dynamic, meaning it changes from day to day or week to week.

Is there a way I can incorporate a dynamic concatenate function into my macro?

 

[iliace]

If you can post a mock-up or a sample of the columns as they might occur, in a before-and-after sort of way, you might get more specific guidance.

In general, you can have VBA retrieve the columns as they currently appear, and then loop to concatenate each one, or picking and choosing as needed.

This example takes all the cells in the first row of the active sheet, and concatenates them, then places the result in cell A2 of the active sheet.

Sub ConcatDynCol()
  Dim s As String
  Dim rngRow As Excel.Range
  Dim rngCell As Excel.Range
  
  Set rngRow = Intersect(ActiveSheet.Rows(1), ActiveSheet.UsedRange)
  
  For Each rngCell In rngRow.Cells
    s = s & rngCell.Value
  Next rngCell
  
  ActiveSheet.Range("A2").Value = s
End Sub