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.