Cut data in 2nd row and paste in the end of 1st row

[Dan]

I have a question: in my case I have an Excel worksheet with data that is
broken in two rows per a record. For instance, in 1st row it is read as
"Place" (Column A), (second cell) "Total" (column B) and goes until K column
and then the data goes to next row and starts from column B. What I need it
to write a macro that would cut data from 2nd row and paste at the end of 1st
row. There are total about 106 rows with "broken" data. Can anyone suggest
anything how to do it?

 

[DomThePom]

Sub FixWrappedData()
'assumes that we want to fix a data series in an excel spreadsheet where the
data
'has been wrapped on alternate rows and the wrapped row is indented to the
main row

'select data you want to fix
Dim cell As Range
Dim rngToCut As Range
Dim rngDestination As Range
Dim lngMaxCols As Long

'determine maximum number of columns for this version of excel
lngMaxCols = ActiveSheet.Columns.Count
'for each row of the dataset
For Each cell In Selection.Columns(1).Cells
'if this is a rox to fix - if cell is empty then = indented = wrapped
If IsEmpty(cell) = True Then
'cut the data on this row (from first cell with data to last
cell with data)
Set rngToCut = Range(cell.End(xlToRight), _
cell.Offset(0, lngMaxCols - cell.Column).End(xlToLeft))
'paste to first empty cell to right of data in row above
Set rngDestination = cell.Offset(-1, 0).Offset(0, _
lngMaxCols - cell.Column).End(xlToLeft).Offset(0, 1)
rngToCut.Cut rngDestination
'delete wrapped row
cell.EntireRow.Delete
End If
Next cell
End Sub

 

[Dan]

Hi DomThePom!
Thanks a lot, the macro works perfect - two more questions, if you don't
mind - the 1st cell that is allegedly "empty" in column A has non-printing
characters. How can I get rid of them? I tried CLEAN command and then TRIM
but it didn't work somehow. I go to individual "empty" cell, hit either
delete or backspace, and after that macro works perfect. And the second
question is can you automate macro to run for entire sheet? All I do is
select two rows with "broken" data and then run it, otherwise it doesn't work
for entire sheet. Thank you.
Dan.

 

[DomThePom]

Hi DomThePom!
Thanks a lot, the macro works perfect - two more questions, if you don't
mind - the 1st cell that is allegedly "empty" in column A has non-printing
characters. How can I get rid of them? I tried CLEAN command and then TRIM
but it didn't work somehow. I go to individual "empty" cell, hit either
delete or backspace, and after that macro works perfect. And the second
question is can you automate macro to run for entire sheet? All I do is
select two rows with "broken" data and then run it, otherwise it doesn't work
for entire sheet. Thank you.
Dan.

Hi Dan
Cnane the line

If IsEmpty(cell) = True Then

to something that will recognise your cells with non-printing characters

Re "entire sheet" - I wrote the macro so that it works on the current
selection - select all the data that you want to fix