Forum Discussion

Ed Weyler's avatar
Ed Weyler
Copper Contributor
May 30, 2021
Solved

Fill cells in a column with a repeating term then stop filling when other data ends

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

'
Range("J1").Select
ActiveCell.FormulaR1C1 = "out shelt"
Columns("J:J").Select
Selection.FillDown
End Sub

excel
Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    May 30, 2021

    Ed Weyler 

    Like this:

     

     

    Sub Macro1()
    Dim m As Long
    m = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Range("J1:J" & m).Value = "out shelt"
End Sub

    (Also posted on Microsoft Community)

     

2 Replies

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    May 30, 2021

    Ed Weyler 

    Like this:

     

     

    Sub Macro1()
    Dim m As Long
    m = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Range("J1:J" & m).Value = "out shelt"
End Sub

    (Also posted on Microsoft Community)