Copying Values till the next blank space

[Recycle_Bin]

Hi Please Help me if possible. i run reports but of course the data will be different per report run, so how do i go about checking column A, and copying data till the last blank space, then copying the next value until the next last blank space and so on. it should stop in line with the last value of B.

The Sample Data:
A B
1 100 PCs
2 Laptops
3 Servers
4 Printers
5 200 PCs
6 Laptops
7 Servers
8 400 PCs
9 Laptops
10 600 PCs
11 Laptops
12 Servers
13 800 PCs
14 Laptops
15 Servers
16 Printers
17 900 PCs
18 Laptops
19 Servers
20 Printers


The End Result should look like:
A B
1 100 PCs
2 100 Laptops
3 100 Servers
4 100 Printers
5 200 PCs
6 200 Laptops
7 200 Servers
8 400 PCs
9 400 Laptops
10 600 PCs
11 600 Laptops
12 600 Servers
13 800 PCs
14 800 Laptops
15 800 Servers
16 800 Printers
17 900 PCs
18 900 Laptops
19 900 Servers
20 900 Printers

Please Help if possible.

[teylyn]

Hi,

try this to fill in the blanks

in cell A2 put the formula

=A1

Copy cell A2

Then select the whole column A, hit F5, click "Special", tick "Blanks", hit OK and use your favourite Paste command, for example Ctrl-V

Now all the numbers are filled in.

Next, Copy column A again and use Paste Special - Values to replace the formulas with the actual numbers.

Now you can sort by the numbers in column A

viola!

[Recycle_Bin]

Thanks man, but how do i do it using code? i want to eliminate manual work as i'm trying to set this up for other users

[teylyn]

start the macro recorder,

run through the steps in my post

stop the macro recorder

[Recycle_Bin]

Awesome, thank you so much! It worked