Fill down based on row count in different column

[Ally_D]

Hi all

I have a table of data where I need to fill columns A, B and D with a formula or text contained in the final cell of each of those column. Column C is filled with data and has more rows.

I want to count the number of rows in column C, go to final cell in column A and fill down to match the number of rows containing data in column C. Next proceed to column B and repeat the same fill down and lastly repeat the action in column D.

I have tried the following but it doesn't do anything. Are you able to help please?

Edited to add: the data is in a Table called 'Priorities' therefore I could substitute references to the columns with the column Names.

Sub Autofill_Columns()
      
    Dim lrow As Long
        lrow = Range("C" & Rows.Count).End(xlUp).Row
        Range("A" & lrow, Range("A" & Rows.Count).End(xlUp)).FillDown
        Range("B" & lrow, Range("B" & Rows.Count).End(xlUp)).FillDown
        Range("D" & lrow, Range("D" & Rows.Count).End(xlUp)).FillDown
     
End Sub

fruits.xlsm
	A	B	C	D
1	Priority	Source	Item	Color
2	2	Grocery Store	Apple	Green
3			Pear
4			Kiwi
Sheet2

Cell Formulas
Range	Formula
A2	A2	=1+1

 

[MikeVol]

Hi @Ally_D.
In A2 insert next Formula: =IF($C2<>"",ROW()-1,"") then run your macro. Or delete your's Formula from A2 and insert updated macro:

Option Explicit

Sub Autofill_Columns()

    With ThisWorkbook.Worksheets("Sheet1")   ' Replace with the actual worksheet name

        Dim lrow    As Long
        lrow = .Range("C" & .Rows.Count).End(xlUp).Row

        .Range("A2").FormulaR1C1 = "=IF(RC3<>"""",ROW()-1,"""")"
        .Range("A" & lrow, .Range("A" & .Rows.Count).End(xlUp)).FillDown
        .Range("B" & lrow, .Range("B" & .Rows.Count).End(xlUp)).FillDown
        .Range("D" & lrow, .Range("D" & .Rows.Count).End(xlUp)).FillDown
    End With

End Sub

and run it. I hope this is the solution you were looking for and I was happy to help you. Good luck.

 

[Ally_D]

Hi, thank you for your response however the formula in A2 isn't the problem. I have a formula functioning correctly with manual autofill (the actual formula in cell A2 is irrelevant) but I'm stuck how to code the VBA to do the following:

1) Count how many table rows in column C contain data - in this example there are 3 rows
2) Move to the bottom of column A (cell A2) and autofill the formula down for as many rows as have been counted in column A - in this example I would expect the formula in A2 to fill down to A4 = 3 rows
3) Move to the bottom of column B and repeat
4) Move to the bottom of column D and repeat

If there was an additional row in the table - worksheet row 5 - but column C (cell C5) is blank then I don't want the formula in column A to fill down to A5.

Hopefully this clarifies things.

 

[MikeVol]

Hello. To be honest, I don't understand you at all and I think that other users don't understand you either, what you want to get as a final result. Otherwise, there would be other answers from other users of this forum. Give an example visually as it is and what result you expect.

 

[Tetra201]

@ Ally_D: see if the following code works for you

Sub Autofill_Columns()
    Dim lrow As Long, col
    lrow = Range("C:C").Find("*", , xlValues, xlWhole, xlByRows, xlPrevious).Row
    For Each col In Array("A", "B", "D")
        Range(Range(col & ":" & col).Find("*", , xlValues, xlWhole, xlByRows, xlPrevious), Range(col & lrow)).FillDown
    Next col
End Sub

 

[Ally_D]

@ Ally_D: see if the following code works for you

Sub Autofill_Columns()
    Dim lrow As Long, col
    lrow = Range("C:C").Find("*", , xlValues, xlWhole, xlByRows, xlPrevious).Row
    For Each col In Array("A", "B", "D")
        Range(Range(col & ":" & col).Find("*", , xlValues, xlWhole, xlByRows, xlPrevious), Range(col & lrow)).FillDown
    Next col
End Sub

Thank you so much @Tetra201 - works perfectly!

 

[Ally_D]

Just in case it helps anyone in the future here is the before and after:

BEFORE

AFTER

 

[Tetra201]

Glad to have helped.