Copy and Paste Loop

jh4342

New Member
Joined
Feb 3, 2016
Messages
5
Good afternoon, I am a rookie when it comes to Macros. I believe I have a relatively simple one but am struggling to get it done.

I have an excel sheet with unique identifiers in Column B. The problem I need that Unique identifier Copied and pasted down Column B until I reach the next unique identifier in Column B and continued down the entire sheet. Some might have 20 spaces between, some might have one. This goes on for 59000 rows and I would like to have them populated accordingly if possible? Can anyone help? Let me know ASAP.

Thanks!
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Hi I had to read you post a few times to understand it.
So if i am correct, you have column B with data until row 59000.
But you have allot of blank cells between.
You want to copy down every entry until you find the next entry, then copy that new entry down and so on.

correct?

Dave
 
Upvote 0
Hi I had to read you post a few times to understand it.
So if i am correct, you have column B with data until row 59000.
But you have allot of blank cells between.
You want to copy down every entry until you find the next entry, then copy that new entry down and so on.

correct?
In addition to Dave's question above, I have a question also... how do you determine how many blank cells under the last unique identifier to copy it down to?
 
Upvote 0
Hi Rick

I think jh4342 wants to copy and paste all the blank cells to the next data entry within the column.
Or I think so?

Dave
 
Upvote 0
Hi Rick

I think jh4342 wants to copy and paste all the blank cells to the next data entry within the column.
Or I think so?
That's what he said, but if the last entry is a unique identifier, what makes it special that it does not get copied down like all the other unique identifier. What I am trying to elicit from the OP is whether some other column of data is to be used to determine the last cell to fill down to.
 
Upvote 0
Oh yes, i see what you mean rick.

Dave
 
Upvote 0
Thanks for the quick responses. I have answered the questions below. I really appreciate your help, please let me know if you need anything else.

So if i am correct, you have column B with data until row 59000. (Yes, 58051 to be exact)
But you have allot of blank cells between. (yes, some with just one, some with possibly 50+ blanks in between)
You want to copy down every entry until you find the next entry, then copy that new entry down and so on. (Yes)
how do you determine how many blank cells under the last unique identifier to copy it down to? (its completely random, in a test I ran I populated the column next to it so I could double click the bottom right part of the cell to automatically send it down to the next cell with Data, stop there, and do the same over and over. This works, but I cannot figure out how to make it loop over and over again.)


 
Upvote 0
how do you determine how many blank cells under the last unique identifier to copy it down to? (its completely random, in a test I ran I populated the column next to it so I could double click the bottom right part of the cell to automatically send it down to the next cell with Data, stop there, and do the same over and over. This works, but I cannot figure out how to make it loop over and over again.)
It sounds like you want to copy that last unique value in Column B down to the last row that contains data no matter what column that it is in, so that is what I made the macro below do. See if this does what you want...
Code:
Sub FillInTheBlanksInColumnB()
  Dim LR As Long
  LR = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
  With Range("B2:B" & LR)
    .SpecialCells(xlBlanks).FormulaR1C1 = "=R[-1]C"
    .Value = .Value
  End With
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,144
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top