Automatically Filling Blank Cells

f00gal · Dec 5, 2005

<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

Von Pookie · Dec 5, 2005

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 · Dec 5, 2005

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

A

B

C

D

1

#

FirstName

LastName

2

1

3

4

5

6

7

6

John

Doe

8

1

9

10

3

Jane

Doe

11

1

12

13

14

15

16

17

18

19

9

Paul

Smith

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