Fill Blank Cells from cell that has data below the blank cell

[danjw_98]

I've seen where you can fill blank cells from the above cell, but was needing something that could do the reverse.
thanks...


current:
A1 = blank
A2 = blank
A3 = blank
A4 = data

would like:
a1 = data from a4
a2 = data from a4
a3 = data from a4
a4 = data

 

[njimack]

1. Select A1:A3
2. Enter "=" (without quotes)
3. Press the Down cursor key once
4. Press Ctrl+Enter

 

[danjw_98]

Is there a way to possibly do this with a macro?

 

[njimack]

Yes but you need to post a sample of your data with expected results.

 

[danjw_98]

i am looking to do this for column "a" and the number of blank cells would vary between the cells that have data. thanks...

example:
a1 thru a8 = blank, a9 = fy11q3

a10 thru a15 = blank, a16 = fy11q4

etc.

results:
a1 thru a9 = fy11q3

a10 thru a16 = fy11q4

 

[danjw_98]

I kinda was able to get this working but someone might have a better solution that this, if so please let me know. thanks...


Sub FillBlankCellsFromCellBelow()
Application.ScreenUpdating = False
For x = 1 To 1000

Dim cell As Object

Set Rng = Range("a2:a" & Range("l65536").End(xlUp).Row)
For Each cell In Rng

If cell.Value = "" Then
'cell.Value = cell.Offset(-1, 0).Value
cell.Value = cell.Offset(1, 0).Value
r1 = cell.Value
cell.Value = r1

Else: End If
Next cell

Next x

Application.ScreenUpdating = True
End Sub

 

[Texas Bohemian]

I've seen where you can fill blank cells from the above cell, but was needing something that could do the reverse.
thanks...


current:
A1 = blank
A2 = blank
A3 = blank
A4 = data

would like:
a1 = data from a4
a2 = data from a4
a3 = data from a4
a4 = data

You can do it the same way you fill from top to bottom using GoTo special blanks. Highlight the area as you normally would, then GoTo special blanks. The GoTo blank function default is to activate the upper most blank cell in your highlighted area. You don't want this cell activated if you've got blanks beneath it followed by data that you want to fill upward. So, once you have your blanks highlighted simply press the control key and while depressed, use your mouse and click on the first blank cell above the data you want to fill upward. Then enter "=" (w/out quotes) and arrow down to select the data you want to fill upward. I just tested this on an entire column of data, and it worked like a charm.

 

[abhrat]

You can do it the same way you fill from top to bottom using GoTo special blanks. Highlight the area as you normally would, then GoTo special blanks. The GoTo blank function default is to activate the upper most blank cell in your highlighted area. You don't want this cell activated if you've got blanks beneath it followed by data that you want to fill upward. So, once you have your blanks highlighted simply press the control key and while depressed, use your mouse and click on the first blank cell above the data you want to fill upward. Then enter "=" (w/out quotes) and arrow down to select the data you want to fill upward. I just tested this on an entire column of data, and it worked like a charm.

it really worked like charm!!! thank you

 

[arjun0014]

Commendable solution

You can do it the same way you fill from top to bottom using GoTo special blanks. Highlight the area as you normally would, then GoTo special blanks. The GoTo blank function default is to activate the upper most blank cell in your highlighted area. You don't want this cell activated if you've got blanks beneath it followed by data that you want to fill upward. So, once you have your blanks highlighted simply press the control key and while depressed, use your mouse and click on the first blank cell above the data you want to fill upward. Then enter "=" (w/out quotes) and arrow down to select the data you want to fill upward. I just tested this on an entire column of data, and it worked like a charm.