Nick

Select the column and F5SpecialBlanks and OK

With these blanks selected, right-click and DeleteEntire Row and OK.

BTW Excel has 65536 rows maximum.


Gord Dibben Excel MVP

On Wed, 23 Mar 2005 13:49:03 -0800, Nickornyk
wrote:

Your macro was amazing. It really helped me alot. Maybe you can help with
the spaces I have now.

I have various spaces throughout the spreadsheet. Not always the same
amount of spaces. How do I delete the empty rows so I can run your macro?

I have 10,000 address and my spreadsheet goes down to 67,000.

"Gord Dibben" wrote:

Nick

Are the addresses consistenly 3 rows per your example?

If one column and your data is consistently 3 rows, this macro will work.

Sub ColtoRows_NoError()
Dim Rng As Range
Dim i As Long
Dim j As Long
Dim nocols As Integer
Application.ScreenUpdating = False
Set Rng = Cells(Rows.Count, 1).End(xlUp)
j = 1
On Error Resume Next
nocols = InputBox("Enter Number of Columns Desired")
For i = 1 To Rng.Row Step nocols
Cells(j, "A").Resize(1, nocols).Value = _
Application.Transpose(Cells(i, "A").Resize(nocols, 1))
j = j + 1
Next
Range(Cells(j, "A"), Cells(Rng.Row, "A")).ClearContents
Application.ScreenUpdating = True
End Sub

If more than one original column, post back with more details.

If unfamiliar with macros see David McRitchie's "getting started" site.

http://www.mvps.org/dmcritchie/excel/getstarted.htm


Gord Dibben Excel MVP

On Tue, 22 Mar 2005 16:51:04 -0800, Nickornyk
wrote:

I copied address off the internet. They are all isted in column A.

example:
First Place Awards
1234 Fifth Avenue
New York, NY 11111

How do I get row 2 to column b and row 3 to column c?

What I want.
A B C
First Place Awards 1234 Fifth Avenue New York, NY 11111

I have 10,000 address all in this one column that I'd like to have seperated
into 3 columns. I don't have the time to cut and paste.

Any way I can do this?