Blank Lines
 

I imported a 'contact list' db into an excel worksheet.

It has many areas needing fixing before I can eventually import the
info I need into an access db table.

The first one I want to address is the fact that the contacts (3 rows
each) are separated by empty rows, and not the same number of rows.

I want to know if there is a way to programmatically find, say, 5
empty rows and delete 4 of them, then find 4 empty rows and delete 3
of them, find 3 and delete 2, and finally find 2 and delete one,
ending with a single blank row between each contact.

Actually, I am not even sure this is the best way to approach the
problem, but it is the best idea I have.

Any help sure will make my day.

Another problem I have is that city state and zip are in the same
field and I need to break them out into separate fields. I know this
has been covered many times, and I am going to go out there and try to
find it - but if you know where it is I sure would be happy if you
would point me to it.

Thanks muchly for your time and efforts. I appreciate it a great deal.
Joanne

Blank Lines
 

Joanne,

This is dirty and not real quick, but it might work for you. It deletes all
the blanks and then inserts a blank back in between each one. Back up your
data before running!:

Sub test()
Dim cell As Range
Dim last_row As Long

Application.ScreenUpdating = False
last_row = Range("A" & Rows.Count).End(xlUp).Row
On Error Resume Next ' in case no blanks
Range("A1:A" & last_row).SpecialCells(xlCellTypeBlanks).EntireRow .Delete
On Error GoTo 0
last_row = Range("A" & Rows.Count).End(xlUp).Row
For Each cell In Range("A1:A" & last_row)
If cell.Row < 1 Then
If cell.Value < "" And cell.Offset(-1, 0).Value < "" Then
cell.EntireRow.Insert shift:=xlUp
End If
End If
Next cell
Application.ScreenUpdating = True
End Sub

hth,

Doug

"Joanne" wrote in message
...
I imported a 'contact list' db into an excel worksheet.

It has many areas needing fixing before I can eventually import the
info I need into an access db table.

The first one I want to address is the fact that the contacts (3 rows
each) are separated by empty rows, and not the same number of rows.

I want to know if there is a way to programmatically find, say, 5
empty rows and delete 4 of them, then find 4 empty rows and delete 3
of them, find 3 and delete 2, and finally find 2 and delete one,
ending with a single blank row between each contact.

Actually, I am not even sure this is the best way to approach the
problem, but it is the best idea I have.

Any help sure will make my day.

Another problem I have is that city state and zip are in the same
field and I need to break them out into separate fields. I know this
has been covered many times, and I am going to go out there and try to
find it - but if you know where it is I sure would be happy if you
would point me to it.

Thanks muchly for your time and efforts. I appreciate it a great deal.
Joanne

Blank Lines
 

Hi Joanne

Try this to end up with one blank row between each contact. (Watch out for
text wrapping)

Sub Test()
With ActiveSheet
..Range("A65000").End(xlUp).Offset(5, 0) = "XX"
..Range("A:A").SpecialCells(xlCellTypeBlanks).Offs et(1,
0).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
..Range("A65000").End(xlUp).EntireRow.Delete
End With
End Sub


Assuming the zip codes are 8 characters long, contain both letters and
numbers and are the last element of the details for each contact, try the
following to place the zip code on its own line.

Sub Test2()
Dim r As Range
With ActiveSheet
..Range("B:B").Insert Shift:=xlToRight
..Range("A65000").End(xlUp).Offset(2, 0) = "XX"
Set r = _
..Range(.Range("A2"), .Range("A" &
Rows.Count).End(xlUp)).SpecialCells(xlCellTypeBlan ks)
r.FormulaR1C1 = "=RIGHT(R[-1]C,8)"
..UsedRange.Formula = .UsedRange.Value2
r.Offset(-1, 1).FormulaR1C1 = _
"=TRIM(LEFT(RC[-1],LEN(RC[-1])-LEN(R[1]C[-1])))"
..UsedRange.Formula = .UsedRange.Value2
r.Offset(-1, 0).FormulaR1C1 = "=RC[1]"
..UsedRange.Formula = .UsedRange.Value2
r.Offset(1, 0).EntireRow.Insert Shift:=xlDown
..Range("B:B").Delete Shift:=xlToLeft
..Range("A65000").End(xlUp).EntireRow.Delete
End With
End Sub

--

Regards

William

XL2003




"Joanne" wrote in message
...
|I imported a 'contact list' db into an excel worksheet.
|
| It has many areas needing fixing before I can eventually import the
| info I need into an access db table.
|
| The first one I want to address is the fact that the contacts (3 rows
| each) are separated by empty rows, and not the same number of rows.
|
| I want to know if there is a way to programmatically find, say, 5
| empty rows and delete 4 of them, then find 4 empty rows and delete 3
| of them, find 3 and delete 2, and finally find 2 and delete one,
| ending with a single blank row between each contact.
|
| Actually, I am not even sure this is the best way to approach the
| problem, but it is the best idea I have.
|
| Any help sure will make my day.
|
| Another problem I have is that city state and zip are in the same
| field and I need to break them out into separate fields. I know this
| has been covered many times, and I am going to go out there and try to
| find it - but if you know where it is I sure would be happy if you
| would point me to it.
|
| Thanks muchly for your time and efforts. I appreciate it a great deal.
| Joanne