Fill in missing data

[josh_m]

using software at work to output some data to xl, however it is not very user friendly. when our store list has more than one member name it does not output all the store data associated with that name, only for the first one (it's easier to look at and understand than explain). anyhow is there any easy way to fill in the empty fields with the correct data?
*for this sample i have deleted several columns, the actual output file is much larger if that makes a difference. there are 10K+ stores, each with 1-6 names associated with it.





<style type="text/css">
table.tableizer-table {border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif; font-size: 12px;} .tableizer-table td {padding: 4px; margin: 3px; border: 1px solid #ccc;}
.tableizer-table th {background-color: #104E8B; color: #FFF; font-weight: bold;}
</style>

<table class="tableizer-table">
<tr class="tableizer-firstrow"><th>Store Number</th><th>Street Address 1</th><th>City</th><th>State</th><th>Zip</th><th>Project Status</th><th>Member Name</th></tr> <tr><td>5710</td><td>123 main st</td><td>Albuquerque</td><td>NM</td><td>87121</td><td>Completed</td><td>Pat</td></tr> <tr><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td>Eric</td></tr> <tr><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td>Tom</td></tr> <tr><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td>Jane</td></tr> <tr><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td>Scott</td></tr> <tr><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td>Rick</td></tr> <tr><td>2067</td><td>64 Elm St</td><td>WEST UNION</td><td>SC</td><td>29696</td><td>Completed</td><td>Sam</td></tr> <tr><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td>Steve</td></tr> <tr><td>7099</td><td>89 Rose Way</td><td>CLOVER</td><td>SC</td><td>29710</td><td>Completed</td><td>Allie</td></tr> <tr><td>13859</td><td>250 Dillon Lane</td><td>Jackson</td><td>TN</td><td>38301</td><td>Completed</td><td>Eric</td></tr> <tr><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td>Tom</td></tr> <tr><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td>Jane</td></tr> <tr><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td>Eric</td></tr> <tr><td>13254</td><td>700 Hollywood Dr</td><td>Jackson</td><td>TN</td><td>38301-2704</td><td>Completed</td><td>Tom</td></tr> <tr><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td>Jane</td></tr> <tr><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td>Jay</td></tr> <tr><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td>Jerry</td></tr> <tr><td>8744</td><td>510 Central Ave. West</td><td>Hampton</td><td>IA</td><td>50441</td><td>Completed</td><td>Art</td></tr> <tr><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td>Marty</td></tr> <tr><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td>Roger</td></tr> <tr><td>8012</td><td>1210 Hwy 150</td><td>Palmyra</td><td>IN</td><td>47164</td><td>Completed</td><td>Timmy</td></tr> <tr><td>3215</td><td>5001 Spring Road</td><td>Rome</td><td>GA</td><td>30165</td><td>Completed</td><td>Emma</td></tr> <tr><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td>Carl</td></tr> <tr><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td>Reid</td></tr> <tr><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td>Peter</td></tr> <tr><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td>Jordan</td></tr> <tr><td>77</td><td>259 Church St.</td><td>Patterson</td><td>LA</td><td>70392</td><td>Completed</td><td>Dale</td></tr> <tr><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td>Dennis</td></tr> <tr><td>12985</td><td>57 Ryman Way</td><td>Englewood</td><td>OH</td><td>45322</td><td>Completed</td><td>Randy</td></tr></table>

 

[pboltonchina]

I'm sure you'll be able to expand this to suit your needs

Option Explicit

Sub CopyDown()
Dim LastRow As Long
Dim i As Long
    LastRow = Range("A65536").End(xlUp).Row
    For i = 2 To LastRow
        If Range("A" & i).Value = "" Then
        Range("A" & i - 1).Copy Destination:=Range("A" & i)
    End If
    Next i
End Sub

 

[josh_m]

thank you. that does exactly what i needed.
however, it only works on column "A"
i am not very proficient in vba, could you perhaps modify/show me how it would repeat through column "G"?
or at least 1 more column and maybe i could take it from there and modify to run through each column needed

 

[Jonmo1]

Try

Sub test()
Dim LR As Long
LR = Cells(Rows.Count, "A").End(xlUp).Row
With Range("A1:G" & LR)
    .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
    .Value = .Value
End With
End Sub

 

[josh_m]

thank you jonmo1. that worked fantastic and i see how it can be modified to work on however many columns needed.
you just don't know how much time this is going to save me!

 

[Jonmo1]

Glad to help, thanks for the feedback.