PDA

View Full Version : Find maximum number of rows with text, then fill accordingly



some_evil
08-08-2014, 10:43 PM
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
08-09-2014, 01:20 AM
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
08-09-2014, 04:26 AM
Sub M_snb()
cells(1).currentregion.columns(3)="snb"
End sub

shrivallabha
08-09-2014, 05:35 AM
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
08-10-2014, 03:18 PM
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
08-11-2014, 01:00 AM
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
08-11-2014, 01:15 AM
Sub M_snb()
cells(1).currentregion.columns(rows(1).find("Fill text").column)="snb"
End Sub