Find maximum number of rows with text, then fill accordingly

[some_evil]

Hi Shrivallabha,

Your below code is the easiest for me to follow and understand, but is there a way to modify so as it looks for the row with the heading "FILL COLUMN" to fill in the text? The reason I mention this is because sometimes in my tables there will be data in the columns to the right of this, so it wont be the 'LastRow'.

Welcome to VBAX. Here's one more route:

Public Sub FillColumn()
Dim lColFill As Long, lLastRow As Long
lcollfill = Range("1:1").Find("FillColumn").Column                          'Header row assumed 1
lLastRow = Cells.Find("*", , , , xlByRows, xlPrevious).Row                  'Last filled row
Range(Cells(2, lcollfill), Cells(lLastRow, lcollfill)).Value = "Fill Text"  'Text filled in
End Sub

Hi Mikerickson,

With regards to your below code, am I correct in saying that this is hard coded to fill column C? As per my above comment I really am hoping for the code to search for the heading 'FillColumn' and then fill that column accordingly.

With Sheet1.Range("A:B") 
     With Application.Intersect(.EntireColumn, .SpecialCells(xlCellTypeConstants).EntireRow) 
        .Offset(1, 2).Resize(.Rows.Count - 1, 1).Value = "FILL TEXT" 
     End With
End With

Cheers everyone for your help.