Skip Blanks

[Guest]

I have seven column

Each column has variable rows of enteries (ranging from row1 to row30

Since these are captured from another calculator, the number of enteries would keep on changin
Ex
A B C D E F
26 Sep 28 Sep 01 Oct 03 Oct 05 Oct 08 Oct 13 Oc
27 Sep 29 Sep 02 Oct 04 Oct 06 Oct 09 Oct 14 Oc
28 Sep 30 Sep 03 Oct 05 Oct 07 Oct 10 Oct 15 Oc
01 Oct 08 Oct 11 Oct
12 Oct
13 Oct

Enteries in other cells are formulated to "

I need these enteries in one column in a sequential manner (For Ex
26 Se
27 Se
28 Se
28 Se
29 Se
30 Se
& skip out the "

Please help! Thanx for your patienc

Regard
Sachin

 

[Jason Morin]

Try this macro. It'll compress all the columns into 1
column on a new sheet. Press ALT+F11, Insert > Module, and
paste in the code. Then run.

Sub OneColumn()

''''''''''''''''''''''''''''''''''''''''''
'Macro to copy columns of variable length'
'into 1 continous column in a new sheet '
'by Jason Morin 4/1/04 '
''''''''''''''''''''''''''''''''''''''''''

Dim colndx As Long
Dim ws As Worksheet
Dim myrng As Range
Dim idx As Integer


Set ws = ActiveWorkbook.activesheet
ilastcol = Cells(1, Columns.Count).End(xlToLeft).Column

Sheets.Add.Name = "Alldata"

idx = Sheets("Alldata").Index
Sheets(idx + 1).Activate

For colndx = 1 To ilastcol

ilastrow = ws.Cells(Rows.Count, colndx).End(xlUp).Row
jlastrow = Sheets("Alldata").Cells(Rows.Count, 1) _
..End(xlUp).Row

Set myrng = Range(Cells(1, colndx), _
Cells(ilastrow, colndx))
With myrng
.Copy Sheets("Alldata").Cells(jlastrow + 1, 1)
End With
Next

Sheets("Alldata").Rows("1:1").EntireRow.Delete

End Sub

--------
HTH
Jason
Atlanta, GA

-----Original Message-----
I have seven columns

Each column has variable rows of enteries (ranging from row1 to row30)

Since these are captured from another calculator, the

number of enteries would keep on changing

Ex:
A B C

D E F G

 

[Guest]

Thanx for the response
Actually am getting an syntax erro

am not a programmer myself, is there any worksheet function to do the sam

Regard

Sachin