Moving data

[Stuart]

I have data as follows:

Row colB colC

50 data..... 5
51 data.....
52 data.......

and

50 data......
51 data........
52 data........ 5

each of the above represents a dataset
there is always at least one blank row between datasets

The first dataset is fine. In the second, I would like to
move '5' in C52 up to C50.

I would like to loop through col C and test if the value
in col C is at the start of the dataset.....if not, then
move it to the start

For Each c In .UsedRange.Columns("C")
If Not IsEmpty(c) Then
QtyRw = c.Row
StartRw = c.Offset(0, -1).End(xlUp).End(xlDown).Row
If StartRw < QtyRw Then
.Range("c" & StartRow) = c.Value
.Range("c" & QtyRw).ClearContents
End If
End If
Next

Is there a better approach please?

Regards.

 

[Guest]

Stuart
For a purely manual approach

1) in D50 enter =C50+C51+C5

2) Select cells D50 through D5

3) Pull fill handle to bottom of dat

This will copy the formula into each forth row. As long as column C only has one entery per data set you can just add the three cells. If the data is text use & instead of

4) Copy and Paste_Special>Values over the original data

Good Luck
Mark Graesse
(e-mail address removed)

----- Stuart wrote: ----

I have data as follows

Row colB col

50 data.....
51 data....
52 data......

an

50 data.....
51 data.......
52 data........

each of the above represents a datase
there is always at least one blank row between dataset

The first dataset is fine. In the second, I would like t
move '5' in C52 up to C50

I would like to loop through col C and test if the valu
in col C is at the start of the dataset.....if not, the
move it to the star

For Each c In .UsedRange.Columns("C"
If Not IsEmpty(c) The
QtyRw = c.Ro
StartRw = c.Offset(0, -1).End(xlUp).End(xlDown).Ro
If StartRw < QtyRw The
.Range("c" & StartRow) = c.Valu
.Range("c" & QtyRw).ClearContent
End I
End I
Nex

Is there a better approach please

Regards