VBA: find data in row, cut, and paste to a specific row above (same column)

[Legacy 171839]

Hi all,

Pulling the hair I have left out.

I have several rows of data, making up a working day in 5 min increments.

I want to copy and paste the data from the lower rows into the 1st row, to finish with 1 line of data for the day.

If there is an over lap, the data from the lines below should take precedence.

Example of the data I have, and what I want the macro to do are in the 2 tables below.

I hope I have been able to articulate what I am trying to acheive in a way that makes sense.

Any help at all will be greatly received.

Kind regards[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]John[/TD]
[TD]John[/TD]
[TD]John[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]John[/TD]
[TD]John[/TD]
[TD]John[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Dave[/TD]
[TD]Dave[/TD]
[TD]Dave[/TD]
[TD]Dave[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Pete[/TD]
[TD]Pete[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Alex[/TD]
[TD]Alex[/TD]
[TD]Alex[/TD]
[TD]Alex[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]John[/TD]
[TD]John[/TD]
[TD]Dave[/TD]
[TD]Dave[/TD]
[TD]Dave[/TD]
[TD]Dave[/TD]
[TD]Pete[/TD]
[TD]Pete[/TD]
[TD]John[/TD]
[TD]Alex[/TD]
[TD]Alex[/TD]
[TD]Alex[/TD]
[TD]Alex[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[JLeno]

You could use a nested IF function in Excel (without VBA):
=IF(A5<>"",A5,IF(A4<>"",A4,IF(A3<>"",A3,IF(A2<>"",A2,A1))))

Perhaps this does the trick for you? A VBA solution will also be fairly straightforward I think, but if it's not necessary I would stick to Excel functions.

Does this help you at all?

 

[MickG]

Try this:-

Dim Rng As Range, Dn As Range
For Each Dn In UsedRange
    If Not IsEmpty(Dn) Then
        Cells(1, Dn.Column) = Dn
    End If
Next Dn
UsedRange.Offset(1).Delete

 

[Legacy 171839]

Thank you both for your help.

JLeno - of course, I was looking so hard for a VBA fix, I missed the obvious solution....

Thanks alot, great help indeed...!