Auto fill down a row without copy and pasting

[steve400243]

Hello, Wondering about the best way to fill down a column without copy and pasting. I have multiple entries in a column as shown in the attachment. Would like a way to copy down the rows to the next entry then stop and copy down that entry, etc. Hope that makes sense. Thank you!

RFCU4032547
SMCU1221331
BMOU5992301
YMLU9539086

 

[steve400243]

Surely there must be other columns with data. If you can provide the column letter for that we can find it programmatically.
Example:
View attachment 45329

 

[Gokhan Aycan]

Alright, then instead of LastRow = 50, try the following:

LastRow = Range("A1").CurrentRegion.Rows.Count

If any confidential data is in there, edit and delete

 

[steve400243]

Alright, then instead of LastRow = 50, try the following:

LastRow = Range("A1").CurrentRegion.Rows.Count

If any confidential data is in there, edit and delete

Even Better - Thanks Again, Have a great weekend!

 

[Peter_SSs]

You should be able to fill them all pretty much at once rather than looping individually through the rows. Something like:

Sub Fill_Blanks()
  With Range("J2:J" & ActiveSheet.UsedRange.Rows.Count)
    .SpecialCells(xlBlanks).FormulaR1C1 = "=R[-1]C"
    .Value = .Value
  End With
End Sub

 

[steve400243]

You should be able to fill them all pretty much at once rather than looping individually through the rows. Something like:

Sub Fill_Blanks()
  With Range("J2:J" & ActiveSheet.UsedRange.Rows.Count)
    .SpecialCells(xlBlanks).FormulaR1C1 = "=R[-1]C"
    .Value = .Value
  End With
End Sub

Many Thanks Peter_SSs, I appreciate the Code. Much Quicker. Can you explain this Line for me?

 .SpecialCells(xlBlanks).FormulaR1C1 = "=R[-1]C"

 

[Peter_SSs]

Many Thanks Peter_SSs, I appreciate the Code. Much Quicker.

You're welcome. Glad to help.

Can you explain this Line for me?

Sure. In every blank cell in the column it puts a formula that retrieves the value that is in the cell immediately above it.
So if J2 had a value and then several blank cells, those blank cells would now contain these formulas:
J3 would contain =J2
J4 would contain =J3
J5 would contain =J4
etc

To see it in action, instead of just running that code, put your cursor in the code and press F8 until that line has been processed, then have a look at the cells in column J that were originally blank.

 

[steve400243]

You're welcome. Glad to help.


Sure. In every blank cell in the column it puts a formula that retrieves the value that is in the cell immediately above it.
So if J2 had a value and then several blank cells, those blank cells would now contain these formulas:
J3 would contain =J2
J4 would contain =J3
J5 would contain =J4
etc

To see it in action, instead of just running that code, put your cursor in the code and press F8 until that line has been processed, then have a look at the cells in column J that were originally blank.

Thank you again, So much to learn. I appreciate your time to explain that.

 

[Peter_SSs]

Cheers