Fill in Blanks from Cell above if not blank

jwalkerack

Board Regular
Joined
Jun 19, 2013
Messages
81
Hello , I have a large Spread sheet that contains some incomplete data within one of the columns the team column see below . The of data inside the column
needs to be filled down for all the blank spaces until it reaches a cell with data

NAME
Project name
DataType
TEAM
JACK
Player_Review
Midfielder
MAN U
JOHN
Player_Review
Striker
JIM
Player_Review
Striker
TIM
Player_Review
Striker
THOMMY
Player_Review
Striker
JOHNNY
Player_Review
Striker
ALICE
Player_Review
Midfielder
ARSENAL
MART
Player_Review
Striker
MARY
Player_Review
Striker
DEBBIE
Player_Review
Striker
JOAN
Player_Review
Striker
TONY
Player_Review
Striker
TOMMY
Player_Review
Midfielder
MAN CITY
TIMMY
Player_Review
Striker
DINNY
Player_Review
Striker
JINNY
Player_Review
Striker
JOHNNY
Player_Review
Striker
DAVID
Player_Review
Striker
THOMAS_B
Player_Review
Midfielder
LIVERPOOL
TONY
Player_Review
Striker
PAULIE
Player_Review
Striker
EDDIE
Player_Review
Striker
TREVOR
Player_Review
Striker
MARY_S
Player_Review
Striker

<tbody>
</tbody>

The desired output would be something like this , is there some code that could do this quickly
NAME
Project name
DataType
TEAM
JACK
Player_Review
Midfielder
MAN U
JOHN
Player_Review
Striker
MAN U
JIM
Player_Review
Striker
MAN U
TIM
Player_Review
Striker
MAN U
THOMMY
Player_Review
Striker
MAN U
JOHNNY
Player_Review
Striker
MAN U
ALICE
Player_Review
Midfielder
ARSENAL
MART
Player_Review
Striker
ARSENAL
MARY
Player_Review
Striker
ARSENAL
DEBBIE
Player_Review
Striker
ARSENAL
JOAN
Player_Review
Striker
ARSENAL
TONY
Player_Review
Striker
ARSENAL
TOMMY
Player_Review
Midfielder
MAN CITY
TIMMY
Player_Review
Striker
MAN CITY
DINNY
Player_Review
Striker
MAN CITY
JINNY
Player_Review
Striker
MAN CITY
JOHNNY
Player_Review
Striker
MAN CITY
DAVID
Player_Review
Striker
MAN CITY
THOMAS_B
Player_Review
Midfielder
LIVERPOOL
TONY
Player_Review
Striker
LIVERPOOL
PAULIE
Player_Review
Striker
LIVERPOOL
EDDIE
Player_Review
Striker
LIVERPOOL
TREVOR
Player_Review
Striker
LIVERPOOL
MARY_S
Player_Review
Striker
LIVERPOOL

<tbody>
</tbody>
 
Last edited:

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Hi, give this a try:

Code:
Sub m()
Dim r As Range, a As Range
On Error Resume Next
    Set r = Range("D2:D" & Range("A" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If Not r Is Nothing Then
    r.FormulaR1C1 = "=R[-1]C"
End If
For Each a In r.Areas
    a.Value = a.Value
Next a
End Sub
 
Upvote 0
Assuming your table starts in A1:

Select D2:D25
Press F5
Click on Special
Select Blanks and click OK
Type =D2
Hold down CTRL and press ENTER
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

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