VBA code to fill down
 

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?

VBA code to fill down
 

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.
--
Gary''s Student - gsnu200787


"Secret Squirrel" wrote:

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?

VBA code to fill down
 

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" wrote:

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.
--
Gary''s Student - gsnu200787


"Secret Squirrel" wrote:

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?

VBA code to fill down
 

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.
--
Gary''s Student - gsnu200788


"Secret Squirrel" wrote:

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" wrote:

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.
--
Gary''s Student - gsnu200787


"Secret Squirrel" wrote:

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?

VBA code to fill down
 

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

"Secret Squirrel" wrote in
message ...
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" wrote:

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.
--
Gary''s Student - gsnu200787


"Secret Squirrel" wrote:

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?

VBA code to fill down
 

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


"Rick Rothstein (MVP - VB)" wrote in
message ...
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

"Secret Squirrel" wrote in
message ...
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" wrote:

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.
--
Gary''s Student - gsnu200787


"Secret Squirrel" wrote:

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?

VBA code to fill down
 

Works like a charm! Thanks Gary!

"Gary''s Student" wrote:

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.
--
Gary''s Student - gsnu200788


"Secret Squirrel" wrote:

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" wrote:

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.
--
Gary''s Student - gsnu200787


"Secret Squirrel" wrote:

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?