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