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

wrennie

New Member
Joined
May 19, 2023
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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.
 

Attachments

  • Export Example.JPG
    Export Example.JPG
    47.7 KB · Views: 12
  • Formatted Example.JPG
    Formatted Example.JPG
    75.3 KB · Views: 11

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Give this a try:
VBA Code:
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:
VBA Code:
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.
 
Upvote 0
Solution
Give this a try:
VBA Code:
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:
VBA Code:
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!
 
Upvote 0
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?
 
Upvote 0
@leturc , give this a try:
VBA Code:
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.
 
Upvote 1
@leturc , give this a try:
VBA Code:
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!
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,291
Members
448,564
Latest member
ED38

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