Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count cells with fill color and retain code - Mr. Umlas Can you he | Excel Discussion (Misc queries) | |||
auto fill code | Excel Discussion (Misc queries) | |||
can I auto fill zip code column when city is typed | Excel Worksheet Functions | |||
code to fill in missing border lines | Excel Discussion (Misc queries) | |||
Can I auto fill a cell in one worksheet by typing a code number | Excel Worksheet Functions |