Odd fill down formula macro question

[Guest]

I've searched for a solution similar to my dilema, but have not yet found
it.... I'm trying to set up a macro to take an exported spreadsheet from our
company software and modify it so the data can be easily analyzed with a
pivot table. The data exports like it would print the report, so there are
blank cells, like such:

Col A Col B Col C
New Park
Alarms 4253
Appliances 5457
Flooring 5487
Oak Park 2
Alarms 3578
Cabinet Supplier 4872


What I'd like to accomplish is this:

Step 1
Insert a column before Col A (making Col A now Col B)

Step 2
Copy (what is now) B2 to A2

Step 3
Insert a fomula to populate data from Col B into Col A, such as
= If(IsBlank(B3),A2,B3)
then fill down Col A, with the following results:

Col A Col B Col C Col D
New Park New Park
New Park Alarms 4253
New Park Appliances 5457
New Park Flooring 5487
Oak Park 2 Oak Park 2
Oak Park 2 Alarms 3578
Oak Park 2 Cabinet Supplier 4872

With this, I can then use a pivot table to analyze data in Cols A, C & D.

I don't have a pre-defined range for the exported report. Problem with my
formula is that it won't detect the end of the data, but will continue
filling in Col A. The report exports with blank cells in each column. How can
I automate the steps outlined above and identify where to end the macro?

Thanks much!
JCT

PS - I've tried to simulate the data in columns. If it doesn't come across
clearly, let me know. I'm open to another solution. I just need to be able to
analyze the exported data. Thx.

 

[Michael Smith]

So I think your prob is the length of your sheet changes..so fill down
doesn't work necessarily. Sometimes what I'll do is dim the length of
the sheet like this.
Lets say you have a formula in cell A2 that you want copied down.

Range("Z1").Formula = "=COUNTA(A:A)"
Dim LengthofSheet
LengthofSheet = Range("Z1").Text

Range("a2").Copy
Range("a3:a" & LengthofSheet).Select
ActiveSheet.Paste

Good luck!
-Mike

 

[David McRitchie]

Why do you need to create another column. You can use fill down
or an equivalent macro to copy the populated cells in column A down.
Replicate values into blank cells (#fill_empty)
http://www.mvps.org/dmcritchie/excel/fillhand.htm#fill_empty

 

[Guest]

Hey, that's a handy tool I wasn't aware of! (Fumbling my way through
programming... but eager to learn more). I'll give it a try.

Thank you.

 

[Guest]

David - This works great, and was an easy solution. Thanks for the resource.