code needed

[jvoortman]

I have a list of companies along with their address
information going straight down column A. There are extra
spaces between companies and their corresponding
information. I want there to be only 1 blank cell, and not
many. This way I can use code supplied before to sort info
for my purposes.
here is a sample:

A's Supply
207 Hurontario
Collingwood, ON L9Y2M1
705-444-9214
Fax: 705-444-9214









Action Sanitation Products
2 North Street North
Perth, ON K7H3M7
613-264-8440









Advantage Products Company
94 Vine
Hamilton, ON L8R2A9
905-528-6631
Fax: 905-528-1337









Alfa Industrial
101 Dolomite Drive
North York, ON M3J2N1
416-661-5357
Fax: 416-736-7810









Alkachem Products Inc
950 Denison
Markham, ON L3R3K4
905-948-1538









All In One Cleanning Supplies & Paper Products Ltd
20 Hartzell Road
St Catharines, ON L2P1M4
905-682-0110









All Round Building Services
519-623-4907









Allcare Services
418 Hanlan
Woodbridge, ON L4L3T1
905-856-8558









Alpine Sanitation Supply Ltd
24 Ronson Drive
Etobicoke, ON M9W1B4
416-249-7586









Alpine Sanitation Supply Ltd
24 Ronson Drive
Etobicoke, ON M9W1B4
416-744-2120









Alpine Sanitation Supply Ltd
905-577-0355



Altamira Maintenance / Janitorial Ltd
210 Markland Dr
Etobicoke, ON M9C1R2
416-762-9982
Voice: 416-738-5332
Fax: 416-762-2193



http://www.altamiramaintenance.com
(e-mail address removed)






AMRE Supply Ontario Limited
525 First St
London, ON N5V1Z5
519-451-2300
Fax: 519-451-3361









Amway Products Distributors
6446 Thornberry Crescent
Windsor, ON N8T2X1
519-944-5111









Amway Products Distributors
198 Verbena Court
Oshawa, ON L1G3E7
905-576-8635

 

[Peo Sjoblom]

Here is a one way, assume the companies start in A2 and A1 holds the header,
insert a temporary help column B
in B2 put this formula

=IF(AND(A2="",A2<>A1),1,0)

copy down as long as needed, apply autofilter on A and B, from dropdown in A
select Blanks (scroll down until the end)
from dropdown in B select 0 now select all cells below row 1 and do
delete>entire row, take off the autofilter and delete the help column

This will save one empty row

 

[jvoortman]

that worked great, thank you>.........

-----Original Message-----
Here is a one way, assume the companies start in A2 and A1 holds the header,
insert a temporary help column B
in B2 put this formula

=IF(AND(A2="",A2<>A1),1,0)

copy down as long as needed, apply autofilter on A and B, from dropdown in A
select Blanks (scroll down until the end)
from dropdown in B select 0 now select all cells below row 1 and do
delete>entire row, take off the autofilter and delete the help column

This will save one empty row

--

Regards,

Peo Sjoblom




.