Need to replace CurrentRegion in concatenation Macro

[Tim Hanson]

The concatenations macro has work well for me but in a new project I have to dynamically generate multiple Sheets with multiple columns of varying number

The data looks like this:

Col A    Col B    Col C
 aa       ee   
                   mm
       
       
                   nn
       
          uu   
       
 aa                mm

The code uses

With Cells(1).CurrentRegion

which does not select the whole column range due to the blank rows

I have tried but found no way to replace "CurrentRegion" with a different method

I am hoping someone here will know how to do this

Thanks

Sub ConCat()
    Dim rng As Range, r As Range, i As Long
   
    On Error Resume Next
    'This is just an example range each sheet is different
    Set rng = Range("B1,A1,C1")
    On Error GoTo 0
   
    If rng Is Nothing Then Exit Sub
   
    With Cells(1).CurrentRegion
        ReDim a(1 To .Rows.Count, 1 To 1)
        a(1, 1) = "Concat"
       
        For i = 2 To .Rows.Count
            For Each r In rng
                If .Cells(i, r.Column) <> "" Then
                    a(i, 1) = a(i, 1) & IIf(a(i, 1) = "", "", "|") & .Cells(i, r.Column).Value
                End If
            Next
        Next
       
        With .Offset(, .Columns.Count).Resize(, 1)
            .Value = a
        End With
       
    End With
End Sub

 

[Hui]

Try this instead:

Sub ConCat2()
   
  Dim lr As Integer, lc As Integer
  Dim i As Integer, j As Integer
   
  lr = Range("A" & Rows.Count).End(xlUp).Row
  lc = Range("A1").CurrentRegion.Columns.Count
   
  For j = 2 To lr
  For i = lc To 1 Step -1
  If Cells(j, i) <> "" Then
  temp = temp + Cells(j, i) + "|"
  End If

  Next i
  On Error Resume Next
  Cells(j, lc + 1) = Left(temp, Len(temp) - 1)
  temp = ""
  Next j

End Sub

 

[Marc L]

Hi !

Another way is Sheet22.UsedRange instead of Cells(1).CurrentRegion
with cleared / deleted columns after data columns …

Edit : I removed my code 'cause I didn't see the reverse order need …
​

 

[Tim Hanson]

Hui, Marc L, thank you both!

I changed Cells(1).CurrentRegion to ActiveSheet.UsedRange (I thought I tryed using UsedRange but I must not have) which allows me to use the original code and its ability to easily set different ranges

Again Thanks