Hi,

Am Fri, 27 Nov 2020 12:05:20 -0800 (PST) schrieb Xxer Xxes:

Your code work perfectly, as well as your
functions .

for your new requirement try:

Sub Test()
Dim varData() As Variant
Dim i As Long, n As Long

With ActiveSheet
n = 1
For i = 31 To 50
If Len(.Cells(1, i)) 0 Then
ReDim Preserve varData(1 To 2, 1 To n)
varData(1, n) = .Cells(1, i)
varData(2, n) = .Cells(1, i).Column
n = n + 1
End If
Next
.Range("A1").Resize(2, n - 1) = varData
End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016