Find maximum number of rows with text, then fill accordingly

[some_evil]

Hi All,

I am trying to get some code that will look at the below sheet, find the column with the most rows filled in, then fill another row based on its heading.

So to make a simple scenario, I am trying to look at the sheet below (note fields can be in any order, this is not fixed), find that column A is the longest with 9 attributes down, then find the column with the heading "FillColumn" (this is fixed parameter) then fill will pre-defined text "FILL TEXT" in as many rows as the longest column (in this case, column A)....

Before

	A	B	C
1	Surname	FirstName	FillColumn
2	Smith	William
3	Brown	Jacob
4	Lee	Liam
5	Wilson
6	Martin	Mason
7	Patel	Benjamin
8	Taylor
9	Wong

AFTER

	A	B	C
1	Surname	FirstName	FillColumn
2	Smith	William	FILL TEXT
3	Brown	Jacob	FILL TEXT
4	Lee	Liam	FILL TEXT
5	Wilson		FILL TEXT
6	Martin	Mason	FILL TEXT
7	Patel	Benjamin	FILL TEXT
8	Taylor		FILL TEXT
9	Wong		FILL TEXT

Is this doable? The code I am using at the moment is below, and it relies on the longest column being in column 'F', and the column filled is also fixed as column 'B'.

With ActiveSheet
        .Range("B2:B" & Range("F" & Rows.Count).End(xlUp).Row).Formula = ""FILL TEXT""
    End With

Thanks

[mikerickson]

How about

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

[snb]

Sub M_snb()
  cells(1).currentregion.columns(3)="snb"
End sub

[shrivallabha]

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

[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.

[shrivallabha]

Hello some_evil

Please note this line searches for the Header column:
lcollfill = Range("1:1").Find("FillColumn").Column 'Header row assumed 1
Please adjust the bold part to suit the search word. I took it from your sample posted at post #1. Also note that I have assumed Your Headers will be in Row of Excel.

lLastRow part does not look at "Fill Column" placement. It finds out the overall last data filled row.

Please test the code after making above changes and post back if you face issues.

[snb]

Sub M_snb() 
    cells(1).currentregion.columns(rows(1).find("Fill text").column)="snb" 
End Sub