Here's an odd one

[Ben Greenwood]

I have been given a spreadsheet with over 300 records.
Each record (for some unknown idiotic reason) has a blank
row in between. Is there an easy way to get rid of these?

ALSO...

The first cell of each record (again for reasons unknown)
has a space at the start i.e. " abc"

How can I get rid of these easily?

Any ideas would be much appreciated.

Thanks,

Ben

 

[Harald Staff]

Hi Ben

Here's a small macro that should fix both. It works in the A column, hope
that's where your data is.

Sub Cleanup()
Dim L As Long
Dim S As String
For L = Cells(10000, 1).End(xlUp).Row To 1 Step -1
S = Trim$(Cells(L, 1).Formula)
Select Case S
Case ""
Rows(L).Delete
Case Else
Cells(L, 1).Formula = S
End Select
Next
End Sub

HTH. Best wishes Harald

 

[Ben Greenwood]

Harald,

Thank you so much for your quick response. Unfortunately,
the data starts in column B (why, when A is blank? grr)
and the cells that are the gaps are merged, which is
proving a hell of a pain in moving them.

Is there a simple way you can amend that to affect column
B? (assuming the merged fields won't affect it - the merge
goes from column B through colomn K and appears to serve
no function at all, except to annoy me!)

I'll keep trying to move the data


Ben

 

[Ben Grenwood]

Hi Harald,

I got the data into column A and removed the merged cells,
ran it and all the blank rows are gone! Thankyou!

The leading spaces are still there, however.

 

[Harald Staff]

Hi Harald,

I got the data into column A and removed the merged cells,
ran it and all the blank rows are gone! Thankyou!

The leading spaces are still there, however.

Hi Ben

Change to B column by changing all Cells(L, 1) with Cells(L, 2), also
Cells(10000, 1) to Cells(10000, 2).

Spaces should be removed. Maybe they aren't spaces but some other invisible
signs. With a troublesome cell with a leading space in B1, what does the
following formula return ?
=CODE(B1)

Best wishes Hasrald

 

[NickMinUK]

For future reference ASAP Utilities (Freeware) has ability to bot
delete rows meeting a criteria e.g.blank, and to delete leading
#characters. Very usefull tool. Nic