Previous Topic Next Topic

Combine consecutive numbers in more than 1 c

Closed
Anex - 2 Aug 2009 à 23:20
venkat1926 Posts 1863 Registration date Sunday 14 June 2009 Status Contributor Last seen 7 August 2021 - 3 Aug 2009 à 06:21
Hello,
I have a large data like this in an excel sheet
A D G
B E H
C F

I want to change in it to one column like this

A
B
C
D
E
F
G
H

Any function or formula please?
Related:

1 response

Réponse 1 / 1
venkat1926 Posts 1863 Registration date Sunday 14 June 2009 Status Contributor Last seen 7 August 2021 811
3 Aug 2009 à 06:21
I presume the letters are different columns that is A D G are in column A,column B,column C respectively. from A1 down without any column headings.

(if not use data-texttocolmns with delimiter as "space" to separate the letters into different columns)

try this macro 

Sub test()
Dim rng As Range, c As Range
Worksheets("sheet2").Cells.Clear
With Worksheets("sheet1")
Set rng = Range(.Range("a1"), .Range("a1").End(xlDown))
For Each c In rng
c.EntireRow.Copy
With Worksheets("sheet2")
.Cells(Rows.Count, "a").End(xlUp).Offset(1, 0).PasteSpecial Transpose:=True
End With
Next c
End With
With Worksheets("sheet2")
Set rng = Range(.Range("a2"), .Range("a2").End(xlDown))

rng.Sort key1:=Range("a1"), order1:=xlAscending, header:=xlNo

End With

End Sub