Copy Down formula using the macro

gejo · Sep 26th 2016

forum.ozgrid.com/index.php?attachment/70283/
I am trying to copy down the certain cells from Row4 onwards based on data in Row3. It works as long as there is data in the column A4 onwards. But if the cells from A4 are blank and i run the macro, it copies down the header row. How can i edit the macro so that this does not occur? Sample file attached

**royUK** · Sep 26th 2016

Re: Copy Down formula using the macro

It won't copy down because the Last Row is based on Column A. I can see a need for copying the formula, but why the other data?

You don't need VBA to copy down formulas, you should convert the data to a Table then this will be automatic. Read Overview of Excel Tables

This will work

Code

Sub CopyDownFormulaDandEcolumns()
  Dim Lastrow As Long
  Lastrow = Range("A1").CurrentRegion.Rows.Count + 1
  Range("J3:J" & Lastrow).FillDown
  Range("F3:F" & Lastrow).FillDown
  Range("D3:D" & Lastrow).FillDown


End Sub

gejo · Sep 26th 2016

Re: Copy Down formula using the macro

to make things more clear, Row 1 is the header row. Row 2 is the one with the formula. The data will be entered from Row4 onwards only. based on different scenarios, the number of rows ranges from 1 to n. the macro is used to copy the formula from Row 3 to Row 'n'. In cases where there are data in Col A, the macro works fine. In case, the user runs the macro with no data in the Row4 to Row'n', the macro should not copy anything. Unfortunately, it copies the Row heading to the Row4.

Code

Sub CopyDownFormulaDandEcolumns()
  Dim Lastrow As Long
  Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
  Range("J3:J" & Lastrow).FillDown
  Range("A2").Select
End Sub

Copy Down formula using the macro

Participate now!

Share