Copy text from cell below based on condition

Paul - Apr 15, 2010 at 04:51 AM
 Paul - Apr 15, 2010 at 08:12 AM

I'm looking for a macro that will copy the text from the cell below to replace the cell above where the cell above meets a certain criteria.


A1 = ***
A2 = text
A3 = "blank"
A4 = ***
A5 = text 2

I'd like to run the macro to replace the contents of A1 with the contents of A2 (and A4 with A5 contents) only where the criteria set = ***. Giving the following result:

A1 = text
A2 = text
A3 = "blank"
A4 = text 2
A5 = text 2

Is this possible?

Thanks in advance,

1 response

venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Apr 15, 2010 at 07:22 AM
your sample data is like this from A1 down



try this macro

Sub test()
Dim r As Range, c As Range
Set r = Range(Range("A1"), Cells(Rows.Count, "A").End(xlUp).Offset(-1, 0))
For Each c In r
If c = "***" Then
c = c.Offset(1, 0)
End If
Next c
End Sub

As you are messing up with the data please save the original data safely somewhere.
Excellent, that's exactly what I needed.

Thanks very much,