Autofill down to last Blank in Range.

[grady121]

I have a specific range on my sheet e.g. ("G10:G21"). (12 cells)
Within the range I’m expecting a variable No. of cells to be filled, but at the bottom of the range. It may have only 1 cell filled, or 11 filled. But there will always be a blank at the top in G10.

What I want to do is fill cell G10 with the initial text, that I can then increment down to the last Blank cell in the range.

E.g. cell G10 = “Test 1”, G11 will autofill “Test 2”, G12 will autofill “Test 3” etc. filling only the blank cells and stopping at the first filled cell.


Any help appreciated.

 

[SteveO59L]

something like

Dim c As Range, rng
Dim cnt_1 As Integer
Set rng = Range("g10:g21")
cnt_1 = 1
For Each c In rng
If c.Value = "" Then
c.Value = "Test" & cnt_1
cnt_1 = cnt_1 + 1
End If
Next c

 

[Fluff]

How about

Sub FilDwn()
   Range("G10").Value = "Test 1"
   Range("G10").AutoFill Range("G10:G21"), xlFillSeries
End Sub

 

[grady121]

Thanks for your replies guys.

Fluff, although simpler, it just filled all cells in the range, overwriting the initial text in the bottom cells I wanted to keep.

So SteveO59L, I've used your suggestion and it works as required.

Thanks for both suggestions.