Our forums are currently in maintenance mode and the ability to post is disabled. We will be back up and running as soon as possible. Thanks for your patience!


Fill cells in a column with the same term then stop filling when data ends

By weylered ·
WIN 10, Excel 2016.  I volunteer IT at a homeless center.  We use a sign in sheet drawn from Salesforce, downloaded into Excel then massaged by a macro to produce the printed list.  They want one column to have the term "shelter out" in every cell one column to mark where the client stayed the night before.

I can get the column to fill each cell correctly but I need it to stop filling when there is no more data in the other columns.  So, one day the list will have 651 clients, the next day it might have 670.

Below is the fill function from the macro.  How do I get it to stop when there is no data in adjoining cells?
Sub Macro1()
' Macro1 Macro

ActiveCell.FormulaR1C1 = "out apt shelt"
End Sub

Thanks for the help!
Thread display: Collapse - | Expand +

All Answers

Collapse -


by Kees_B Moderator In reply to Fill cells in a column wi ...

You'll have to write a loop, starting in J1, and go down one cell at a time until the other cells in the current row are empty. Then stop.

A good programmer knows to handle the special case where row 1 is already empty. I don't think it's necessary to handle the special case when you're at the very bottom of the spreadsheet (there's a maximum of around 1 million rows in an .xlsx file).

Related Discussions

Related Forums