VBA script that will allow me to copy the unit # in column C to the empty cells below untill it reaches the next unit #, then copies that uni

[frankafarah]

I would like to create a VBA script that will allow me to copy the unit # in column C to the empty cells below untill it reaches the next unit #, then copies that unit # to the empty cells below it untill it reaches the next unit# and so on.

I have attached a sample excel spreadsheet.

Really appreciate any help.

Thanks

 

[Alex Blakenburg]

You can't attach a spreadsheet here. Ideally you use XL2BB to post a small representative sample of your data.
Instructions on using this tool can be found here: XL2BB Add-in.
Alternative share some sample data using one of the sharing platforms such as Drop Box, Google drive etc.

In the meantime see if below does what you are after.
It currently assumes your data starts on Row 2 and that Column B will always have an entry in the last row of your table.
Change these to suit your data.

Sub FillDownUnit()

    Dim ws As Worksheet
    Dim rngC As Range, arrC As Variant
    Dim firstDataRow As Long, lastRow As Long
    Dim UnitNo As Variant                                   ' If its definitely numeric then Long would be preferable
    Dim i As Long
    
    Set ws = ActiveSheet
    firstDataRow = 2                                        ' If headings on Row 1 then first data row is 2. Change if required
    lastRow = ws.Range("B" & Rows.Count).End(xlUp).Row      ' Change column B to whatever column is going to have data on all lines
    Set rngC = ws.Range("C" & firstDataRow & ":C" & lastRow)
    arrC = rngC.Value
    
    For i = 1 To UBound(arrC)
        If arrC(i, 1) <> "" Then
            UnitNo = arrC(i, 1)
        Else
            arrC(i, 1) = UnitNo
        End If
    Next i
    
    rngC.Value = arrC

End Sub

 

[Peter_SSs]

Welcome to the MrExcel board!

My guess is that you want to turn something like this ..

frankafarah.xlsm
	A	B	C	D
1	Hdr1	Hdr2	Hdr3	Hdr4
2	data	data	Unit 23	data
3	data	data		data
4	data	data	Unit 5	data
5	data	data		data
6	data	data		data
7	data	data		data
8	data	data		data
9	data	data	Unit 100	data
10	data	data		data
Sheet1

.. into this (which is what Alex's code does)

frankafarah.xlsm
	A	B	C	D
1	Hdr1	Hdr2	Hdr3	Hdr4
2	data	data	Unit 23	data
3	data	data	Unit 23	data
4	data	data	Unit 5	data
5	data	data	Unit 5	data
6	data	data	Unit 5	data
7	data	data	Unit 5	data
8	data	data	Unit 5	data
9	data	data	Unit 100	data
10	data	data	Unit 100	data
Sheet1

If that is what you want, here is another approach you could try with a copy of your workbook.

Sub Fill_Units()
  With Range("C2:C" & Range("A" & Rows.Count).End(xlUp).Row)
    .SpecialCells(xlBlanks).FormulaR1C1 = "=R[-1]C"
    .Value = .Value
  End With
End Sub

 

[frankafarah]

Hi Peter
Thanks so much. I received quite a few suggestions, but yours was the most concise. Worked perfectly
Frank

 

[Peter_SSs]

Thanks so much.

You're welcome. Thanks for the follow-up.

I received quite a few suggestions,

Hmm, so I now see. For the future please note the following.

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:
VBA script that will allow me to copy the unit # in column C to the empty cells below until it reaches the next unit #, then copies that unit #
VBA script that will allow me to copy the unit # in column C to the empty cells below unti [SOLVED]
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.