Fill Help

How_Do_I

Well-known Member
Joined
Oct 23, 2009
Messages
1,817
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
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

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,817
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
14,470
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
14,470
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,675
Messages
5,838,718
Members
430,566
Latest member
ChanchalSingh

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