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:

excel1.JPG



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:

excel2.JPG


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
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!
 
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,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.
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