Automatically Filling Blank Cells

f00gal

New Member
Joined
Dec 5, 2005
Messages
2
<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
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
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
Joined
Mar 12, 2002
Messages
11,454
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
Joined
Dec 5, 2005
Messages
2
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,119,002
Messages
5,575,497
Members
412,670
Latest member
Khin Zaw Htwe
Top