Removing blank rows

[legg]

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]

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?

 

[Gord Dibben]

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

 

[legg]

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]

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]

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

 

[Dave]

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

 

[legg]

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

 

[legg]

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