Fill Help

[How_Do_I]

Hello, I have something like this... How would I fill the blank cells in please...

B3 and B4 need to equal 2nd Guards Brigade
B6 needs to equal 3rd Guards Brigade etc..

Excel Workbook
	B
2	2nd Guards Brigade
3
4
5	3rd Guards Brigade
6
7	Divisional Troops
8
9
10
11	Divisional Mounted Troops
12	Divisional Royal Artillery
13
14	Divisional Royal Engineers
Sheet1

 

[ewuchatka]

You will find an answer here very clear

https://www.ablebits.com/office-addins-blog/2014/05/02/fill-blanks-excel/

 

[AliGW]

Select B2 - grab the black square bottom right of the cell and drag down. Repeat for the remaining entries.

 

[ewuchatka]

What AliGW suggests is obviously an option but time consuming. It is much easier to follow the instructions in the link provided which will take you a minute to do and when you have multiple entries like that it will be the most efficient way of dealing with the blanks

Select B2 - grab the black square bottom right of the cell and drag down. Repeat for the remaining entries.

 

[How_Do_I]

You will find an answer here very clear

https://www.ablebits.com/office-addins-blog/2014/05/02/fill-blanks-excel/

Very good thanks...

 

[How_Do_I]

Yes, I used the link.. I must have been doing something wrong with the tip given by AliGW as the very first drag down places "3rd Guards Brigade" in B3 as opposed to "2nd Guards Brigade"...

 

[MARK858]

Just a couple of VBA codes to add to the solutions (the 2nd one is basically the same as what is being done manually in the Ablebits link).

Sub FillCell1()
    Dim fCell As Range
    On Error Resume Next

    For Each fCell In Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row). _
        SpecialCells(xlCellTypeBlanks).Areas
        fCell.Value = fCell(1).Offset(-1).Value
    Next

    On Error GoTo 0
End Sub

Sub FillCell2()
    With Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row)
        .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
        .Value = .Value
    End With
End Sub

As for the AliGW suggestion right click the bottom right hand corner, hold, drag down and select Copy cells from the box that appears.

 

[ewuchatka]

Edit>Goto...Special..., choose Blanks, hit OK, type =B2 then press CTRL+ENTER

Yes, I used the link.. I must have been doing something wrong with the tip given by AliGW as the very first drag down places "3rd Guards Brigade" in B3 as opposed to "2nd Guards Brigade"...

 

[MARK858]

Edit>Goto...Special..., choose Blanks, hit OK, type =B2 then press CTRL+ENTER

That is the same method as is in the link, it is also more generic to type = and Ctrl + up arrow rather than =B2.