Fill down based on row count in different column

Ally_D

New Member
Joined
Oct 17, 2024
Messages
10
Office Version
  1. 2016
Platform
  1. Windows
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.

VBA Code:
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
ABCD
1PrioritySourceItemColor
22Grocery StoreAppleGreen
3Pear
4Kiwi
Sheet2
Cell Formulas
RangeFormula
A2A2=1+1
 
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:
VBA Code:
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
@ Ally_D: see if the following code works for you
VBA Code:
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
 
Upvote 0
Solution
@ Ally_D: see if the following code works for you
VBA Code:
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!
 
Upvote 0
Just in case it helps anyone in the future here is the before and after:

BEFORE
1740475820056.png


AFTER
1740475827961.png
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top