Sub RTranspose()
Dim a&, b%
With Sheets(1)
a = .UsedRange.Rows.Count + .UsedRange.Row - 1
For b = 1 To 2
Sheets(2).Cells(b, 1).Resize(1, a) = Application.Transpose(Intersect(.UsedRange, .Columns(b)))
Next
End With
End Sub
Function VLOOKUP2(Table As Variant, SearchColumnNum As Long, SearchValue As Variant, _
N As Long, ResultColumnNum As Long)
Dim i As Long, iCount As Long
If TypeName(Table) = "Range" Then Table = Intersect(Table.Parent.UsedRange, Table).Value
VLOOKUP2 = ""
For i = 1 To UBound(Table)
If Table(i, SearchColumnNum) = SearchValue Then iCount = iCount + 1
If iCount = N Then
VLOOKUP2 = Table(i, ResultColumnNum)
Exit For
End If
Next i
End Function
Sub RTranspose()
Dim a&, b%, aa As Range, bb As Range
b = 0
With Sheets(1)
a = .UsedRange.Rows.Count + .UsedRange.Row - 1
For Each aa In .Range(.Cells(.UsedRange.Row + 1, 2), .Cells(a, 2))
If Len(aa) > 0 Then
If b = 0 Then b = aa.Row: Set bb = aa Else Set bb = Union(bb, aa)
ElseIf b > 0 Then
.Range("H" & b).Resize(1, bb.Cells.Count) = Application.Transpose(bb)
b = 0
End If
Next
End With
End Sub