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

manvit

New Member
Joined
Oct 31, 2018
Messages
19
Hi All :),
My input is like this .

DateordernumName
1/2/2019A23423Alex
1/2/2019B54534John
5/2/2019E454Joe
4/2/2019C64354Ruby

<tbody>
</tbody>

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.

DateordernumName
1/2/2019A23423Alex
1/2/2019A23423
1/2/2019A23423
1/2/2019
A23423
1/2/2019B54534John
1/2/2019B54534
1/2/2019B54534
5/2/2019E454Joe
5/2/2019
E454
4/2/2019C64354Ruby

<tbody>
</tbody>

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
 
Last edited:

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,303
Office Version
  1. 365
Platform
  1. Windows
How about
Code:
Sub manvit()
   With Range("A2", Range("B" & Rows.Count).End(xlUp))
      .SpecialCells(xlBlanks).FormulaR1C1 = "=r[-1]c"
      .Value = .Value
   End With
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,303
Office Version
  1. 365
Platform
  1. Windows
Sorry, I don't understand
 

manvit

New Member
Joined
Oct 31, 2018
Messages
19

ADVERTISEMENT

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

manvit

New Member
Joined
Oct 31, 2018
Messages
19
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,303
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,307
Office Version
  1. 2010
Platform
  1. Windows
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

New Member
Joined
Oct 31, 2018
Messages
19
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 ?
 

Watch MrExcel Video

Forum statistics

Threads
1,108,969
Messages
5,525,961
Members
409,673
Latest member
Riseee

This Week's Hot Topics

Top