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?