Removing Empty Rows and selecting Specific Rows

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 " [email protected]" 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

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 "
> [email protected]" 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 "
> [email protected]" 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

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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 "
> [email protected]" 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" <[email protected]> wrote in
> message news:[email protected]...
> >
> > 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 "
> > [email protected]" 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" <[email protected]>
wrote in message news:[email protected]...
> 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" <[email protected]> wrote in
>> message news:[email protected]...
>> >
>> > 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 "
>> > [email protected]" 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