Autofill down to last Blank in Range.

grady121

Active Member
Joined
May 27, 2005
Messages
385
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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
 
Upvote 0
How about
Code:
Sub FilDwn()
   Range("G10").Value = "Test 1"
   Range("G10").AutoFill Range("G10:G21"), xlFillSeries
End Sub
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top