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?

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" wrote:

> 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?

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" wrote:

> 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" wrote:
>
> > 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?

"Dan" wrote:

> 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" wrote:
>
> > 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" wrote:
> >
> > > 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?

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