I noted a post in January where someone wanted to insert blank rows in
every other row of a worksheet

I've got the reverse issue.

A database created from a text input has a blank row from the original
double-spacing - but not every second row.

How do I remove blank rows in 50,000 lines of data?

I don't know whether he blank rows carry some baggage from the
conversion - was a copy out of a text file with space-separated values
and double CR between lines (rows).

This is for someone wanting a semiconductor data list that can
eventually be sorted by column values. Will work as is, but is twice
as big as needed.

When currently sorted, the blank rows are not shifted out of the
tables.

RL

What happens if you (or that someone) just sorts the data?

Don't the "blanks" get sorted to the top or the bottom--or at least grouped
together?



legg wrote:
>
> I noted a post in January where someone wanted to insert blank rows in
> every other row of a worksheet
>
> I've got the reverse issue.
>
> A database created from a text input has a blank row from the original
> double-spacing - but not every second row.
>
> How do I remove blank rows in 50,000 lines of data?
>
> I don't know whether he blank rows carry some baggage from the
> conversion - was a copy out of a text file with space-separated values
> and double CR between lines (rows).
>
> This is for someone wanting a semiconductor data list that can
> eventually be sorted by column values. Will work as is, but is twice
> as big as needed.
>
> When currently sorted, the blank rows are not shifted out of the
> tables.
>
> RL

--

Dave Peterson

If the rows do not sort out then you are probably correct in surmising some type
of baggage in blank cells.

Sub TRIM_EXTRA_SPACES()
Dim Cell As Range
For Each Cell In Selection
If (Not IsEmpty(Cell)) And _
Not IsNumeric(Cell.Value) And _
InStr(Cell.Formula, "=") = 0 _
Then Cell.Value = Application.Trim(Cell.Value)
Next
End Sub

Note: if the baggage is is the non-breaking space character 160 you can try an
Edit>Replace

what: Alt + 0160 (on numpad)

with: nothing

replace all

When done, F5>Special>Blanks>OK>Edit>Delete>Entire Rows


Gord Dibben MS Excel MVP

On Tue, 29 Apr 2008 16:10:03 -0400, legg <legg@nospam.magma.ca> wrote:

>I noted a post in January where someone wanted to insert blank rows in
>every other row of a worksheet
>
>I've got the reverse issue.
>
>A database created from a text input has a blank row from the original
>double-spacing - but not every second row.
>
>How do I remove blank rows in 50,000 lines of data?
>
>I don't know whether he blank rows carry some baggage from the
>conversion - was a copy out of a text file with space-separated values
>and double CR between lines (rows).
>
>This is for someone wanting a semiconductor data list that can
>eventually be sorted by column values. Will work as is, but is twice
>as big as needed.
>
>When currently sorted, the blank rows are not shifted out of the
>tables.
>
>RL

On Tue, 29 Apr 2008 15:45:35 -0500, Dave Peterson
<petersod@verizonXSPAM.net> wrote:

>What happens if you (or that someone) just sorts the data?
>
>Don't the "blanks" get sorted to the top or the bottom--or at least grouped
>together?
>
Actually, it's even worse than I thought.

A worksheet won't sort. Tried reversing order (12,000 lines) without
effect.

Text should at least sort in alpha-numerical order, from my
experience.

yow

RL

Are you selecting the range to sort or allowing excel to guess what it should
be.

If you're not selecting the range first, try that. Excel can often guess
wrong--especially with blank rows and/or blank columns.

legg wrote:
>
> On Tue, 29 Apr 2008 15:45:35 -0500, Dave Peterson
> <petersod@verizonXSPAM.net> wrote:
>
> >What happens if you (or that someone) just sorts the data?
> >
> >Don't the "blanks" get sorted to the top or the bottom--or at least grouped
> >together?
> >
> Actually, it's even worse than I thought.
>
> A worksheet won't sort. Tried reversing order (12,000 lines) without
> effect.
>
> Text should at least sort in alpha-numerical order, from my
> experience.
>
> yow
>
> RL

--

Dave Peterson

On Tue, 29 Apr 2008 13:57:30 -0700, Gord Dibben <gorddibbATshawDOTca>
wrote:

>If the rows do not sort out then you are probably correct in surmising some type
>of baggage in blank cells.
>
>Sub TRIM_EXTRA_SPACES()
>Dim Cell As Range
> For Each Cell In Selection
> If (Not IsEmpty(Cell)) And _
> Not IsNumeric(Cell.Value) And _
> InStr(Cell.Formula, "=") = 0 _
> Then Cell.Value = Application.Trim(Cell.Value)
> Next
>End Sub
>
>Note: if the baggage is is the non-breaking space character 160 you can try an
>Edit>Replace
>
>what: Alt + 0160 (on numpad)
>
>with: nothing
>
>replace all
>
>When done, F5>Special>Blanks>OK>Edit>Delete>Entire Rows
>
>
>Gord Dibben MS Excel MVP

I tried the macro. Got a long wait, but no apparent effect or change
in sorting behavior.

The edit function can't to find the character, in the sheet or
selected lines.

RL

Hi,
Do you really have blank XL rows between you data, or do you have a blank
line at the bottom of each XL row?
Dave

On Tue, 29 Apr 2008 16:49:40 -0500, Dave Peterson
<petersod@verizonXSPAM.net> wrote:

>Are you selecting the range to sort or allowing excel to guess what it should
>be.
>
>If you're not selecting the range first, try that. Excel can often guess
>wrong--especially with blank rows and/or blank columns.

That seemed to do the trick.

With all data selected and specifying a sort by column, the blank
lines got ousted without losing row association.

Many thanks.

RL

On Tue, 29 Apr 2008 15:05:01 -0700, Dave
<Dave@discussions.microsoft.com> wrote:

>Hi,
>Do you really have blank XL rows between you data, or do you have a blank
>line at the bottom of each XL row?
>Dave

No, it was a new enumerated line, and not grouped or associated with
the preceding line. Selecting all of the data on the page and sorting
by selected column got rid of the blanks in a trial worksheet.

I was used to just having to pick a single column cell to do this
previously.

12000 lines are now 6000 without loss of row associations.

Thanks for the help.

RL