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?!

 

[gledister]

it gives me compile error

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

 

[MrKowz]

If you require a macro, a tested alternative:

Public Sub FillColA()
Dim rng1    As Range, _
    rng2    As Range
Application.ScreenUpdating = False
Set rng1 = Range("A1")
Do
    If rng1.Offset(1, 0).Value = "" Then
        Set rng2 = rng1.Offset(1, 0).End(xlDown).Offset(-1, 0)
        Range(rng1, rng2).Value = rng1
        Set rng1 = rng2.Offset(1, 0)
        Set rng2 = Nothing
    End If
Loop Until rng1 = Range("A" & Rows.Count).End(xlUp)
Application.ScreenUpdating = True
End Sub

 

[Weaver]

Ah, that wasn't mine anyway. Post #4.

The reason I ask is because your original post said you wanted to go to the bottom of the worksheet and I'm not sure if the 'manual' method will do that, owing to the way 'blank cells' are defined by Excel. If you only need it to fill to the bottom of the current dataset then that would be okay.

 

[gledister]

brans1982 excuse me...it works perfectly.

MrKowz your first solution helped me and thats all I needed.

Now am responding to all the solutions for being kind to all these kind effort everyone is giving to my problem

Thank you again guys

 

[RSXchin]

This way, it doesn't matter what version you use

Sub LoopTest()
    For i = 1 To Rows.Count
        If Cells(i, 1) = "" Then
            Cells(i, 1) = Cells(i - 1, 1)
        End If
    Next i
End Sub

 

[Weaver]

brans1982 excuse me...it works perfectly.

MrKowz your first solution helped me and thats all I needed.

Now am responding to all the solutions for being kind to all these kind effort everyone is giving to my problem

Thank you again guys

Lol, sometimes we can be a little too helpful!

W

 

[RSXchin]

lulz. I got kindof excited

 

[gledister]