I have a list of email addresses in one single column.

This list is large so I need some way of shortcutting what I need t
do.

This entire column of email addresses is separated by 2 empty row
between each email address.

1) Instead of clicking on each one and deleting them one by one, i
there a quicker method to delete all empty rows?

2) The list of email addresses is imported.
All the emaill addresses have a space in front of them. For example
" Do you see the extra space in front of the emai
address?

How do I remove that single space with some sort of shortcut for th
entire column?

Appreciate the help,

J

--
Jethea
-----------------------------------------------------------------------
Jetheat's Profile: http://www.excelforum.com/member.php...fo&userid=1007
View this thread: http://www.excelforum.com/showthread.php?threadid=39511

Hi Jetheat!

This is a pretty easy and common task. The trick here is to delete rows
bottom up. You may try the following macro, see the comments to make the
adjustments you need, and *SAVE* your workbook before playing with it:

Sub DeleteEmptyRows()
Dim i As Integer 'Long if last row number 32767

'Assuming the list is in the first worksheet
With ThisWorkbook.Worksheets(1)
'Substitute 1000 by the number of the last empty row
For i = 1000 To 1 Step -1
'Assuming data is on the first column
'Assuming first cell on the row is really empty
If .Cells(i, 1) = "" Then .Rows(i).Delete
Next i
End With
End Sub

Best,

Rafael

"Jetheat" wrote:


I have a list of email addresses in one single column.

This list is large so I need some way of shortcutting what I need to
do.

This entire column of email addresses is separated by 2 empty rows
between each email address.

1) Instead of clicking on each one and deleting them one by one, is
there a quicker method to delete all empty rows?

2) The list of email addresses is imported.
All the emaill addresses have a space in front of them. For example "
" Do you see the extra space in front of the email
address?

How do I remove that single space with some sort of shortcut for the
entire column?

Appreciate the help,

JH


--
Jetheat
------------------------------------------------------------------------
Jetheat's Profile: http://www.excelforum.com/member.php...o&userid=10073
View this thread: http://www.excelforum.com/showthread...hreadid=395116

Sorry Jetheat, I forgot to mention that another easy way of doing it is
turning the auto filter on and then selecting non-blanks cells. Do that and
copy the filtered list to other worksheet. It's easier if you're not used to
VBA...

Best,

Rafael

"Jetheat" wrote:


I have a list of email addresses in one single column.

This list is large so I need some way of shortcutting what I need to
do.

This entire column of email addresses is separated by 2 empty rows
between each email address.

1) Instead of clicking on each one and deleting them one by one, is
there a quicker method to delete all empty rows?

2) The list of email addresses is imported.
All the emaill addresses have a space in front of them. For example "
" Do you see the extra space in front of the email
address?

How do I remove that single space with some sort of shortcut for the
entire column?

Appreciate the help,

JH


--
Jetheat
------------------------------------------------------------------------
Jetheat's Profile: http://www.excelforum.com/member.php...o&userid=10073
View this thread: http://www.excelforum.com/showthread...hreadid=395116

Thanks Rafael, thats great. I used the second method coz I don't even
know how to get into VBA.

Anyway, now I have a slightly different problem which hopefully you can
help with.

I have 2 columns of email addresses. I have imported them from
different sources.

I need to remove the duplicate entries so I placed one set under the
other.

Then I tried the Filter ~ Advanced Filter trick.

It didn't remove any because I found that the 2nd set does not match
the first set because it has a lot of trailing edge spaces after the
email address.

What can I do to remove these trailing edge spaces and make them the
same as the 1st set?

JH


--
Jetheat
------------------------------------------------------------------------
Jetheat's Profile: http://www.excelforum.com/member.php...o&userid=10073
View this thread: http://www.excelforum.com/showthread...hreadid=395116

And there is any easier way.

Select all the used rows in a single column (where the empty cells are
representative of all the data)
goto the Edit menu and select GoTo, select Special, select Blanks.
Than go to the Edit menu and select Delete, select Entire Rows.

This is pretty quick and works great....

--
steveB

Remove "AYN" from email to respond
"Jetheat" wrote in
message ...

I have a list of email addresses in one single column.

This list is large so I need some way of shortcutting what I need to
do.

This entire column of email addresses is separated by 2 empty rows
between each email address.

1) Instead of clicking on each one and deleting them one by one, is
there a quicker method to delete all empty rows?

2) The list of email addresses is imported.
All the emaill addresses have a space in front of them. For example "
" Do you see the extra space in front of the email
address?

How do I remove that single space with some sort of shortcut for the
entire column?

Appreciate the help,

JH


--
Jetheat
------------------------------------------------------------------------
Jetheat's Profile:
http://www.excelforum.com/member.php...o&userid=10073
View this thread: http://www.excelforum.com/showthread...hreadid=395116

That´s what I like the most in Excel, many ways of doing the same thing!

By the way, I just noticed we forgot to answer the second part of Jethead's
question. To eliminate leading spaces (and trailing as well) use the TRIM
function.

Regards,

Rafael

"STEVE BELL" wrote:

And there is any easier way.

Select all the used rows in a single column (where the empty cells are
representative of all the data)
goto the Edit menu and select GoTo, select Special, select Blanks.
Than go to the Edit menu and select Delete, select Entire Rows.

This is pretty quick and works great....

--
steveB

Remove "AYN" from email to respond
"Jetheat" wrote in
message ...

I have a list of email addresses in one single column.

This list is large so I need some way of shortcutting what I need to
do.

This entire column of email addresses is separated by 2 empty rows
between each email address.

1) Instead of clicking on each one and deleting them one by one, is
there a quicker method to delete all empty rows?

2) The list of email addresses is imported.
All the emaill addresses have a space in front of them. For example "
" Do you see the extra space in front of the email
address?

How do I remove that single space with some sort of shortcut for the
entire column?

Appreciate the help,

JH


--
Jetheat
------------------------------------------------------------------------
Jetheat's Profile:
http://www.excelforum.com/member.php...o&userid=10073
View this thread: http://www.excelforum.com/showthread...hreadid=395116

Yep!

Get 3 people in a room and you can get 30 possible solutions to the same
problem...

--
steveB

Remove "AYN" from email to respond
"Rafael Guerreiro Osorio"
wrote in message ...
That´s what I like the most in Excel, many ways of doing the same thing!

By the way, I just noticed we forgot to answer the second part of
Jethead's
question. To eliminate leading spaces (and trailing as well) use the TRIM
function.

Regards,

Rafael

"STEVE BELL" wrote:

And there is any easier way.

Select all the used rows in a single column (where the empty cells are
representative of all the data)
goto the Edit menu and select GoTo, select Special, select Blanks.
Than go to the Edit menu and select Delete, select Entire Rows.

This is pretty quick and works great....

--
steveB

Remove "AYN" from email to respond
"Jetheat" wrote in
message ...

I have a list of email addresses in one single column.

This list is large so I need some way of shortcutting what I need to
do.

This entire column of email addresses is separated by 2 empty rows
between each email address.

1) Instead of clicking on each one and deleting them one by one, is
there a quicker method to delete all empty rows?

2) The list of email addresses is imported.
All the emaill addresses have a space in front of them. For example "
" Do you see the extra space in front of the email
address?

How do I remove that single space with some sort of shortcut for the
entire column?

Appreciate the help,

JH


--
Jetheat
------------------------------------------------------------------------
Jetheat's Profile:
http://www.excelforum.com/member.php...o&userid=10073
View this thread:
http://www.excelforum.com/showthread...hreadid=395116

Thanks for the help guys, all working

Super


--
Jetheat
------------------------------------------------------------------------
Jetheat's Profile: http://www.excelforum.com/member.php...o&userid=10073
View this thread: http://www.excelforum.com/showthread...hreadid=395116