copy-paste vba? do-loop?

[gledister]

we have this column:

Excel Workbook
	A
1	1
2
3
4	2
5
6
7
8
9
10	3
11
12
13
14
15
16
17
18
19	5
20
21
22
23
24
25
26
27
Sheet1

and what we want is:

Excel Workbook
	A
1	1
2	1
3	1
4	2
5	2
6	2
7	2
8	2
9	2
10	3
11	3
12	3
13	3
14	3
15	3
16	3
17	3
18	3
19	5
20	5
21	5
22	5
23	5
24	5
25	5
26	5
27	5
28	5
Sheet1

and the last one goes till the end of the worksheet.

Anyone got any idea?!

 

[MrKowz]

Try this:

1. Highlight Column A
2. Edit>Goto (if you use Excel 2007, you can find GoTo under Home>Find&Select)
3. Special>Blanks
4. Place in the formula bar =A1
5. Use CTRL+ENTER to enter the formula

 

[JackDanIce]

What definies the number of 5's you copy down? This can do as a start:

Sub Assimilation()
Range("A1").Copy Range("A1").Resize(3, 1)
Range("A4").Copy Range("A4").Resize(6, 1)
End Sub

But for completeness, you probably want to place this into a loop with a variable that works out how many rows you want to copy down but that's difficult to know when you don't define your end/last row

 

[Weaver]

Something like:

Sub test2()
    Application.ScreenUpdating = False
    With Columns(1)
        .Cells(.Rows.Count, 1).Value = .Cells(.Rows.Count, 1).End(xlUp).Value
        .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=r[-1]c"
        .Value = .Value
    End With
End Sub

 

[gledister]

MrKowz,

Your solutions are always AWESOME!

 

[gledister]

Mr Jack,

That does not work for me.

Thanks anyway.

 

[gledister]

Weaver,

That does not work for me too.

Thanks anyway.

 

[Weaver]

Weaver,

That does not work for me too.

Thanks anyway.

Could you explain how it failed please?

 

[gledister]

it made my excel freeze 3 times...

I dont know how to explain that.

 

[brans1982]

This loop will take a while but it will do the trick...

Sub LoopTest()

For i = 1 to 1048576 (65536 - Excel 2003 or less)

If Cells(i,1) = "" Then

Cells(i,1) = Cells(i-1,1)

End If

Next i

End Sub