Repeat the value until the occurrence/presence of a value using macros

[manvit]

Hi All ,
My input is like this .

[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]ordernum[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD]1/2/2019[/TD]
[TD]A23423[/TD]
[TD]Alex[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/2/2019[/TD]
[TD]B54534[/TD]
[TD]John[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5/2/2019[/TD]
[TD]E454[/TD]
[TD]Joe[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4/2/2019[/TD]
[TD]C64354[/TD]
[TD]Ruby[/TD]
[/TR]
</tbody>[/TABLE]

In this above excel sheet, i need to repeat the values in 1st and 2nd column until the occurrence of a new value.



i.e the output should be like this.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]ordernum[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD]1/2/2019[/TD]
[TD]A23423[/TD]
[TD]Alex[/TD]
[/TR]
[TR]
[TD]1/2/2019[/TD]
[TD]A23423[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/2/2019[/TD]
[TD]A23423[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/2/2019
[/TD]
[TD]A23423[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/2/2019[/TD]
[TD]B54534[/TD]
[TD]John[/TD]
[/TR]
[TR]
[TD]1/2/2019[/TD]
[TD]B54534[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/2/2019[/TD]
[TD]B54534[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5/2/2019[/TD]
[TD]E454[/TD]
[TD]Joe[/TD]
[/TR]
[TR]
[TD]5/2/2019
[/TD]
[TD]E454[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4/2/2019[/TD]
[TD]C64354[/TD]
[TD]Ruby[/TD]
[/TR]
</tbody>[/TABLE]

please NOTE: the change needs to only happen in column 1 and 2.

I am beginner in Macros, anybody help with code and its explanation would be much appreciated.


Thanks,
Manvit

 

[Fluff]

Ok, I hadn't seen post#6 as we posted at the same time.
The code looks for all blank cells in cols A & B from row 2 to the last row with data in col B & then inserts a formula that returns the value in the cell above.
The code then converts the formula to a value.

Once you have run the code once, there will be no blank cells & that's why you get the error.

 

[Fluff]

This will deal with the error if there are no blank cells

Sub manvit()
   On Error Resume Next
   With Range("A2", Range("B" & Rows.Count).End(xlUp))
      .SpecialCells(xlBlanks).FormulaR1C1 = "=r[-1]c"
      .Value = .Value
   End With
   On Error GoTo 0
End Sub

 

[manvit]

Thanks a lot Fluff. It did just the right thing .

 

[Fluff]

You're welcome & thanks for the feedback