Copy down to next row where there is data

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
2,332
Office Version
  1. 365
Platform
  1. Windows
I have data running down column A (Part Numbers)
Example: I have a Part number in A2 but don't have another Part number until A45 and then another lets say in A55

How can I copy A2 down to A44 and then A45 down to A54 and then A55 down to last row
(I can determine LastRow by using Column B)

My goal is not to have blanks in column A

The above is just an example. the second part number could be anywhere in column A its not always going to be in "45" and the next will not always be in "55"... But the first will always be in A2

Thanks for your time and help!
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Try this

Code:
Sub Macro4()
  With Range("A2:A" & Range("B" & Rows.Count).End(xlUp).Row)
    .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
    .Value = .Value
  End With
End Sub
 
Upvote 0
wont that just copy A2 all the way down? I need to copy A2 down to where the next Part number is listed in column A (in the example above it would copy down to A44, then I would the code to copy A45 down to A54 and so on)
 
Upvote 0
No, I tried to read the code. I guess I didn't understand it.

My apologies, I will try it now.
 
Upvote 0
Perfect!

Again, my apologies for not trying first.

I appreciate the help very much.
 
Upvote 0
No, I tried to read the code. I guess I didn't understand it.

My apologies, I will try it now.


What the code does is, say, select the empty cells in the range A2 to the last cell according to column B.
Put a formula to take the data from the previous cell.
Finally, change the formula to value.
 
Upvote 0
Just another option as I was passing through....

Code:
Sub FillCell1()
    Dim fCell As Range
    On Error Resume Next

    For Each fCell In Range("A2:A" & Range("B" & Rows.Count).End(xlUp).Row).SpecialCells(4).Areas
        fCell.Value = fCell(1).Offset(-1).Value
    Next

    On Error GoTo 0
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,953
Members
448,535
Latest member
alrossman

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