VBA to copy cell value and paste upto next item

[Chris Turner]

I am trying to find a macro that will help me cut down the amount of time I spend adding machine numbers in a report. I run production and downtime numbers every month.

The data from the database does not populate all the cells with the machine names. I have to go and manually copy and paste the machine names in every empty cell below the name. For example, I would copy Machine 20 in cell A2 and paste it into cells A3:A6, then repeat the saem action for the other machines in Column A. In the actual spreadsheet there are thousands of entries and this takes me several hours of work to do this.

Does anyone have a macro that can find the next machine name in Column A, then copy and paste it into the empty cells below it until it reaches the next machine name. It would repeat the same action all the way down the sheet. Cells in Columns D & E are always populated with a time and a hour value. Cells in Columns A, B & C are not always populated. Appreciate any help.

 

[Chihiro]

No need for VBA. You can use Go To Special.

Select entire column A. Find & Select ->Go to Special. Select Blanks.
Hit OK.

Then enter =A2 (i.e. first non-blank after header) and hit CTRL + ENTER.

If you want values only. Copy the entire column after above operation and paste as value. Do same for column B & C.

FYI: Not sure how you want Column C treated, as C2 is blank.

 

[Chihiro]

If you need VBA. Something like...

Sub Demo()
    With ActiveSheet.UsedRange.Columns(1)
        .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
        .Value = .Value
    End With
End Sub