 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / General Excel Questions / May 2008VBA 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? 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.  SignatureGary''s Student - gsnu200787 > 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? 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? > Something like: > [quoted text clipped - 20 lines] > > these formulas once the import is complete and how do I have the fill down > > code stop where the last row of imports is? 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. SignatureGary''s Student - gsnu200788 > 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 [quoted text clipped - 27 lines] > > > these formulas once the import is complete and how do I have the fill down > > > code stop where the last row of imports is? 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 [quoted text clipped - 33 lines] > > > > these formulas once the import is complete and how do I have the fill down > > > > code stop where the last row of imports is? 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 > Thanks Gary. Follow up question....Instead of having it always fill down > from [quoted text clipped - 38 lines] >> > down >> > code stop where the last row of imports is? 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 > 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. [quoted text clipped - 51 lines] >>> > down >>> > code stop where the last row of imports is? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.