CODEWITHSUNDEEP
excelexcel-formulavba
user_az
user_az

Reputation: 363

Fill blank cells with the value from next value below the blanks

I've seen many post on fill the blank cell from the cell above. But here is something in reverse. Here we can fill the blank cell from the cell below.

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

Upvotes: 1

Views: 2463

Answers (4)

brettdj
brettdj

Reputation: 55672

To fill up - and to handle for separate blank areas:

Sub FillUp()
Dim rng1 As Range
On Error Resume Next
Set rng1 = Range([a1], Cells(Rows.Count, "A").End(xlUp)).SpecialCells(xlBlanks)
On Error GoTo 0
If rng1 Is Nothing Then Exit Sub
rng1.FormulaR1C1 = "=R[1]C"
rng1.Value = rng1.Value
End Sub

Upvotes: 0

Oleg Ushakov
Oleg Ushakov

Reputation: 1545

  1. Select A1
  2. Press "F2"
  3. Enter "=$A$4" (without quotes) or "=R4C1" (in case R1C1 style)
  4. press "Enter"
  5. press Ctrl+C
  6. select A2 and A3 cells
  7. press Ctrl+V

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96753

If you know in advance that A4 is the cell to copy upwards, then:

Sub FillCells()
    Range("A1:A3").Value = Range("A4").Value
End Sub

If you do not know which cell is filled then:

Sub FillCells2()
    Dim N As Long
    N = Cells(Rows.Count, 1).End(xlUp).Row
    Range("A1:A" & N - 1).Value = Range("A" & N).Value
End Sub

Upvotes: 0

user_az
user_az

Reputation: 363

Solution:

1. Select A1:A3
2. Press Enter till the active cell become A3 (just before/above the required value)
3. Enter "=" (without quotes)
4. Press the Down cursor key once/Select the below cell value
5. Press Ctrl+Enter

Similarly for Complete Column we can go through the conventional excel technique and options:

1. Select the Column where you need to fill the blanks
2. Home -> Find & Select -> Select Blank Function(it will select all the blanks in your selection)
3. Now hit enter until reach a cell which is just before the required cell value.
4. Press "="
5. Press the Down key once / Select the below cell value
6. Hit: Ctrl + Enter

Upvotes: 1

Related Questions