Copy and paste at each change in data

stevod

Board Regular
Joined
Aug 21, 2013
Messages
67
Hi Folks,

Looking for Some code to copy and paste but each time the data changes to fill in the blanks. So in column a and b have are the range.
I want the code to copy a and a and paste it to the where b starts... then copy b and b and copy that to c.... and so on (the values in them cells is different)

It needs to find that range and the last row can be used on "C:C" as this has the data to the last row with no gaps



aa
bb
cc
dd
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,762
Office Version
2013
Platform
Windows
I need some help to provide a answer.
With the image you are showing are we seeing column A and B

So when you enter any value into column A or B you want this data copied to column C is that correct.
If not can you help me understand.
 

stevod

Board Regular
Joined
Aug 21, 2013
Messages
67
i basically want the blanks in columns a and b filled
So whatever is in A1:B1 copy this down until it changes.... then find the next range in A:B and copy this down till it changes
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,762
Office Version
2013
Platform
Windows
Not sure what till it changes means.
And copy it down in columns A and B I assume
So what does column C have to do with this:
And you want this to happen with a button or as a sheet change event script
 

stevod

Board Regular
Joined
Aug 21, 2013
Messages
67
so i want it to look like the below,
Yes copy A and B down.... so Select A1:B, copy it and paste it down to banana....
copy banana down to random etc... but the spaces in between is random in size.

Column C can just be used to find the last Row if needed because the last row in C is where it would stop copying to

Sorry, im trying to explain in the best way possible

labellabel
bananabanana
randomramdom
beansbeans


labellabel
labellabel
labellabel
labellabel
bananabanana
bananabanana
bananabanana
bananabanana
randomramdom
randomramdom
randomramdom
randomramdom
beansbeans
beansbeans
beansbeans
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,762
Office Version
2013
Platform
Windows
Try this:
VBA Code:
Sub Copy_Me_Down()
'Modified 3/12/2020 12:24:22 AM EST
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "C").End(xlUp).Row
For i = 2 To Lastrow
If Cells(i, 1).Value = "" Then Cells(i, 1).Resize(, 2).Value = Cells(i - 1, 1).Value
Next
Application.ScreenUpdating = True
End Sub
 

stevod

Board Regular
Joined
Aug 21, 2013
Messages
67
Nearly! its copying Column a into b... i want it to copy both A and B together as if they were both selected
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,762
Office Version
2013
Platform
Windows
Nearly! its copying Column a into b... i want it to copy both A and B together as if they were both selected
I did that because your example showed every thing in column A and B were the same
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,762
Office Version
2013
Platform
Windows
Try this:
VBA Code:
Sub Copy_Me_Down()
'Modified 3/12/2020 12:42:30 AM EST
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "C").End(xlUp).Row
For i = 2 To Lastrow
If Cells(i, 1).Value = "" Then Cells(i, 1).Value = Cells(i - 1, 1).Value
If Cells(i, 2).Value = "" Then Cells(i, 2).Value = Cells(i - 1, 2).Value
Next
Application.ScreenUpdating = True
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,102,344
Messages
5,486,309
Members
407,539
Latest member
ltwkuav

This Week's Hot Topics

Top