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]

How about

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

 

[manvit]

Hi Fluff ,
can you please me the code.

 

[Fluff]

Sorry, I don't understand

 

[manvit]

Hey fluff,
this command .SpecialCells(xlBlanks).FormulaR1C1 = "=r[-1]c"
gives me "run time error 1004, no cells were found "

 

[manvit]

please explain me the code so that i can understand why you the code this way. the code runs perfectly for the first time. second time when i re run it, it gives me an error. why is that ? is it becoz they were no more cells to be repeated ?

 

[Fluff]

In that case you don't have any empty cells in columns A or B
What is in those cells?

 

[Rick Rothstein]

Hey fluff,
this command .SpecialCells(xlBlanks).FormulaR1C1 = "=r[-1]c"
gives me "run time error 1004, no cells were found "

Are those "blank" cells truly blank (absolutely nothing in them) or are there either spaces or formulas in them?

 

[manvit]

blank cells. please read post #6

 

[manvit]

it runs perfectly when i run them for the first time. but my platform( tool which handles data formatting) which runs this macro may run it again and then it gives me an error saying no empty cells found. can we make the error stop somehow? i mean if its error, ignore somehow ?