# Automatically Filling Blank Cells

#### f00gal

##### New Member
<html>
First of all I want to say thank you to everyone who posts on here, so far all of the problems I have had have been answered on the board, but I've come across one I just can't seem to get my mind around. (Probably thinking about it too much, no doubt.)

Here's the deal:

I have a sheet with several blank rows, then a line with a number, a first name and a last name. The number of blank lines is one less than the number in the first column. Like this:

What I want to do is fill it so you have the same first name and last name, but count with the first column. Like this:

I have hundreds of lines like this so doing it by hand is tedious.

Any comments or suggestions would be greatly appreciated!

Beck

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

#### Von Pookie

##### MrExcel MVP
How's this?

Code:
``````Sub test()
Dim FirstRow As Long, LastRow As Long, FinalRow As Long

FirstRow = 2
LastRow = Cells(FirstRow, "A").End(xlDown).Row
FinalRow = Range("A65536").End(xlUp).Row

Do
Range(Cells(LastRow, "A"), Cells(LastRow, "C")).AutoFill Destination:=Range(Cells(FirstRow, "A"), Cells(LastRow, "C"))
FirstRow = LastRow + 1
LastRow = Cells(LastRow, "A").End(xlDown).Row
Loop Until LastRow > FinalRow

End Sub``````

It should basically do the same thing as highlighting the 3 cells in the row then dragging the fill handle up.

#### Yogi Anand

##### MrExcel MVP
Hi Beck:

Welcome to MrExcel Board!

In partial response to your question ...

Let us say we are starting with data as in the following Sheet3 ...
Book1
ABCD
1#FirstNameLastName
21
3
4
5
6
76JohnDoe
81
9
103JaneDoe
111
12
13
14
15
16
17
18
199PaulSmith
20
Sheet3

then

1) select cells B1:C18,
2) do EDIT|GotoSpecial|Blanks
3) hold the CTRL key
4) while holding the CTRL key, select cell B18
5) in the formula bar key-in =B19
6) then while holding the CTRL key press the ENTER Key

that should result in filling in the blanks in columns B and C to your specification as presented in the following ...
Book1
ABCD
1#FirstNameLastName
21JohnDoe
3JohnDoe
4JohnDoe
5JohnDoe
6JohnDoe
76JohnDoe
81JaneDoe
9JaneDoe
103JaneDoe
111PaulSmith
12PaulSmith
13PaulSmith
14PaulSmith
15PaulSmith
16PaulSmith
17PaulSmith
18PaulSmith
199PaulSmith
20
Sheet3 (2)

Let me know if this is what you are looking for.

#### f00gal

##### New Member
It should basically do the same thing as highlighting the 3 cells in the row then dragging the fill handle up.

This is exactly what I needed, thank you so much!

Replies
7
Views
285
Replies
9
Views
416
Replies
0
Views
543
Replies
3
Views
870
Replies
3
Views
151

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.

1,163,501
Messages
5,832,079
Members
430,110
Latest member
Chyke_mxl

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

### Which adblocker are you using?

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

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