Move columns...

[Oogie]

I am working on a macro to reformat an exported report. So fa
everything has been pretty easy going. Now I am at the point where i
gets bit complex.

In column A I have multiple cell that start with the word “Name
(persons name)” the rest of that row is blank. The next row, column
is blank but the rest of the row is filled with numbers. What I wan
the macro to do is find where Ax starts with “Name:” then take Bx+
(the next row starting with B) and move those numbers to the same ro
as A.

Before:
A B C D
1 Name: John Doe
2 10 19.95 15
3 Name: Sue Smith
4 40 15 99
5 Totals 50 34.95 114

After:
A B C D
1 Name: John Doe 10 19.95 15
2
3 Name: Sue Smith 40 15 99
4
5 Totals 50 34.95 114


So, can anyone help me with this?

Thanks in advance!
Oogi

 

[Tom Ogilvy]

If the cells for the continuation row (found in column A) are blank as you
decribe

Sub FixData()
Dim rng as Range, cell as Range
set rng = Columns(1).specialCells(xlconstants,xltextvalues)
for each cell in rng
cell.offset(1,1).Resize(1,100).copy Cell.offset(0,1)
cell.offset(1,1).Resize(1,100).Clearcontents
Next
End sub

 

[Oogie]

Tom,

Thank You for your reply! It works great with one exception, it is
removing the totals...would there be a way of saying if cell Ax
contains "total" to skip?

Thanks Again!
Oogie

 

[Tom Ogilvy]

Sub FixData()
Dim rng as Range, cell as Range
set rng = Columns(1).specialCells(xlconstants,xltextvalues)
for each cell in rng
if instr(1,cell.value,"total",vbTextcompare) = 0 then
cell.offset(1,1).Resize(1,100).copy Cell.offset(0,1)
cell.offset(1,1).Resize(1,100).Clearcontents
end if
Next
End sub