VBA code to fill down

[Secret Squirrel]

I have some VBA code that imports certain columns (a thru g) from another
spreadsheet. I also have two columns of formulas in the spreadsheet that I'm
importing into that I need to fill down after the import is done. Columns H &
I have these formulas. How would I add to my VBA code to have it fill down
these formulas once the import is complete and how do I have the fill down
code stop where the last row of imports is?

 

[Gary''s Student]

Something like:

Sub copy_down()
Dim r As Range, rr As Range, n As Long
With ActiveSheet.UsedRange
n = .Rows.Count + .Row - 1
End With

Set r1 = Range(Cells(1, "H"), Cells(n, "H")).SpecialCells(xlCellTypeBlanks)
Set r2 = Range(Cells(1, "I"), Cells(n, "I")).SpecialCells(xlCellTypeBlanks)
Set r = Union(r1, r2)
For Each rr In r
rr.FillDown
Next
End Sub


But for the fill-down to work, H1 and I1 must not be empty.

 

[Secret Squirrel]

Thanks Gary. Follow up question....Instead of having it always fill down from
H1 & I1 is there a way to have it just fill down from the last row instead of
going back to row 1? Example: I run the macro and it imports 10 rows and then
copies the formula in H1 & I1 down to row 20. Then I do another import from
rows 21 to 45. Can it just fill down from 20 to 45 or does it really not
matter?

 

[Gary''s Student]

Good question! Let's say we do an import and run the macro. The filldown
should work. If we now import more rows below the original import we can
just re-run the macro. This is because the macro only filldowns empty cells,
it won't touch the original imported rows because they have already been
filled.

 

[Rick Rothstein \(MVP - VB\)]

This is where seeing your import code might have helped some. I'm assuming
you have the start and stop rows that you are importing available to you.
Let's say they are stored in variables named FirstRowCopiedInAthruG and
LastRowCopiedInAthruG. I would think this code would then copy the formulas
down as you wanted...

FirstRowCopiedInAthruG = 21
LastRowCopiedInAthruG = 45
ActiveSheet.Range("H" & (FirstRowCopiedInAthruG - 1) & ":I" & _
LastRowCopiedInAthruG).FillDown

Rick

 

[Rick Rothstein \(MVP - VB\)]

Although you are more than likely past Row 1 already, I guess we should
change my code to this in order to be protect against importing to a blank
sheet...

FirstRowCopiedInAthruG = 21
LastRowCopiedInAthruG = 45
If FirstRowCopiedInAthruG = 1 Then
ActiveSheet.Range("H1:I" & LastRowCopiedInAthruG).FillDown
Else
ActiveSheet.Range("H" & (FirstRowCopiedInAthruG - 1) & ":I" & _
LastRowCopiedInAthruG).FillDown
End If

Rick

 

[Secret Squirrel]

Works like a charm! Thanks Gary!

Good question! Let's say we do an import and run the macro. The filldown
should work. If we now import more rows below the original import we can
just re-run the macro. This is because the macro only filldowns empty cells,
it won't touch the original imported rows because they have already been
filled.