Macro to Find Last Cell in Column and Use that Cell to Fill in Formula Row of Other Columns

[wrennie]

Hello! I've searched for something similar to this and have not had much luck. I apologize if it is an easy(ier) answer and I am just missing it.

I have a data set that I download, export, and format multi times weekly. The data set is 125 columns by usually around 45,000 rows. The number of rows varies daily though and can be upwards of 80,000 rows sometimes. I have used a macro built for a while to format a good portion of the data to what I need but I have had to use formulas and manual actions for the remainder. I would like to expand my macros/VBA skills and knowledge and would like to expand/refine the macro.

The portion that I am looking for assistance on is this -

The exported data begins in cell A1. Row 1 is always a header row. I have to first insert three columns before the exported data. So, column A becomes column D.
Columns A - C will contain formulas combining and formatting values from further in the data set.

How do I write a macro to copy and fill the formulas down from row 2 of columns A - C to the last row of data in the now column D?

I am attaching two snips of formatted example data in case it is helpful.

 

[Max1616]

Give this a try:

Sub CopyFormulas()

Dim lrow As Long

lrow = Cells(Rows.Count, 4).End(xlUp).Row

Range("A2:C2").Copy
Range("A3:C" & lrow).PasteSpecial xlPasteFormulas

Application.CutCopyMode = False

End Sub

It will find the last row of data in column D using this:

lrow = Cells(Rows.Count, 4).End(xlUp).Row

Then copy Range(A2:C2) to Range(A3:Cn) where n is the last row populated in column D.

I hope this helps.

 

[wrennie]

Give this a try:

Sub CopyFormulas()

Dim lrow As Long

lrow = Cells(Rows.Count, 4).End(xlUp).Row

Range("A2:C2").Copy
Range("A3:C" & lrow).PasteSpecial xlPasteFormulas

Application.CutCopyMode = False

End Sub

It will find the last row of data in column D using this:

lrow = Cells(Rows.Count, 4).End(xlUp).Row

Then copy Range(A2:C2) to Range(A3:Cn) where n is the last row populated in column D.

I hope this helps.

Thank you, Max! This worked exactly as needed! I appreciate your time and reply!

 

[leturc]

Hello @Max1616 ,

Thank you for the code. I tried to adapt to my situation but in vain.

My issue is I have a SC1 sheet which contains a table imported and another table which is used to concatenate data from other sheets (DASHBOARD).
I need to be able to make count the number of row in the sheet SC1 than make autofill (copy down) on the table in the DASHBOARD sheet which start from column A till BD.

Do you think you can help me?

 

[Max1616]

@leturc , give this a try:

Sub CopyFormulas_Dashboard()

Dim lrow As Long
Dim sht1 As Worksheet
Dim sht2 As Worksheet


Set sht1 = Sheets("SC1")
Set sht2 = Sheets("DASHBOARD")

lrow = sht1.Cells(Rows.Count, 1).End(xlUp).Row

sht2.Range("A2:BD2").Copy
sht2.Range("A3:BD" & lrow).PasteSpecial xlPasteFormulas

Application.CutCopyMode = False

End Sub

This assumes that the data in the sheet SC1 lines up row by row with the data you want in BD.

This also does not delete any formulas, it only extends them down to the length of data in the sheet SC1.

 

[leturc]

@leturc , give this a try:

Sub CopyFormulas_Dashboard()

Dim lrow As Long
Dim sht1 As Worksheet
Dim sht2 As Worksheet


Set sht1 = Sheets("SC1")
Set sht2 = Sheets("DASHBOARD")

lrow = sht1.Cells(Rows.Count, 1).End(xlUp).Row

sht2.Range("A2:BD2").Copy
sht2.Range("A3:BD" & lrow).PasteSpecial xlPasteFormulas

Application.CutCopyMode = False

End Sub

This assumes that the data in the sheet SC1 lines up row by row with the data you want in BD.

This also does not delete any formulas, it only extends them down to the length of data in the sheet SC1.

Thank you very much! İt's works!