Fill Down to Stop at Last Row

[LuisE]

How can I make this to stop at the last used row? It goes to the row 65536

Sub FillDownByColumn()
Do Until y = Range("A1").CurrentRegion.Rows.Count - 1
Selection.End(xlDown).Activate
Range(Selection, Selection.End(xlDown).Offset(-1, 0)).Select
Selection.FillDown
y = Selection.Row
Loop
End Sub

Thanks in advance

 

[Bill Renaud]

Have you single-stepped through your code to watch it at work?

Range(Selection, Selection.End(xlDown).Offset(-1, 0)).Select

... selects the last cell in Column $A down to the last cell on row 655,535
on the first pass through the loop, then fills it with data.

Do Until y = Range("A1").CurrentRegion.Rows.Count - 1

....then has a CurrentRegion of the entire worksheet on the very next loop,
which becomes endless!!!

 

[LuisE]

Thanks Bill,

Yes I have single-stepped through the code.
I'm working with a range with several non-continuos blanks.
Whay it does is to select the cell at the top and blanks below and fill
them. Then it moves to the next cell with blanks underneath and so on. It is
identiying the last row of the range correctly, not 65536. It just doesn't
stop at it.
Do you have any other approach?
Thanks

 

[Bill Renaud]

OK, so if you have the following data, show us what you want to happen
(copy the table below, then fill in the blank cells):

A B C
1 123 456 789
2 234 567 897
3 234 345
4 234 545
5 234 456 456
6 123 456 789
7 234 567
8
9 234

 

[LuisE]

I got it figuere it out.
I wasn't referring to the variable withing the loop.

Thanks