Fill Help

How_Do_I

Well-known Member
Joined
Oct 23, 2009
Messages
1,795
Office Version
  1. 2010
Platform
  1. Windows
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
22nd Guards Brigade
3
4
53rd Guards Brigade
6
7Divisional Troops
8
9
10
11Divisional Mounted Troops
12Divisional Royal Artillery
13
14Divisional Royal Engineers
Sheet1
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

AliGW

Banned
Joined
Mar 9, 2014
Messages
3,628
Select B2 - grab the black square bottom right of the cell and drag down. Repeat for the remaining entries.
 

ewuchatka

New Member
Joined
Nov 23, 2016
Messages
11
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

Well-known Member
Joined
Oct 23, 2009
Messages
1,795
Office Version
  1. 2010
Platform
  1. Windows
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,886
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

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).

Code:
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

Code:
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.
 
Last edited:

ewuchatka

New Member
Joined
Nov 23, 2016
Messages
11
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,886
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,494
Messages
5,602,004
Members
414,490
Latest member
Rip181

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
Top