Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Fill Columns A and B with varying ranges
Thank you so much!!! "Matthew Herbert" wrote: On Jul 23, 5:01 pm, el dee wrote: Thanks again Matt, I appreciate your help. Here are some additional details: 'how do you determine the row for the .AutoFill range? lngStartRow = 2 The start row is determined by a new entry€¦ So, data gets transferred from Sheet(€ťSummary€ť) to Sheet(€śAll_Data€ť). Then sheet(€śsummary€ť) is cleared using the following: (Sorry, I am working on using more block code.) Sub ClearForm() Sheets("Summary").Select Range("B5").Select ActiveCell.FormulaR1C1 = "" Range("E5").Select ActiveCell.FormulaR1C1 = "" Range("I5").Select ActiveCell.FormulaR1C1 = "" Range("I6").Select ActiveCell.FormulaR1C1 = "" For InputRowIndex = 10 To MAX_ROW Range("A" & InputRowIndex).Select If ActiveCell.FormulaR1C1 = "" Then Exit For Else ActiveCell.FormulaR1C1 = "" Range("B" & InputRowIndex).Select ActiveCell.FormulaR1C1 = "" Range("C" & InputRowIndex).Select ActiveCell.FormulaR1C1 = "" Range("D" & InputRowIndex).Select ActiveCell.FormulaR1C1 = "" Range("E" & InputRowIndex).Select ActiveCell.FormulaR1C1 = "" Range("F" & InputRowIndex).Select ActiveCell.FormulaR1C1 = "" Range("G" & InputRowIndex).Select ActiveCell.FormulaR1C1 = "" Range("H" & InputRowIndex).Select ActiveCell.FormulaR1C1 = "" Range("I" & InputRowIndex).Select ActiveCell.FormulaR1C1 = "" Range("J" & InputRowIndex).Select ActiveCell.FormulaR1C1 = "" Range("K" & InputRowIndex).Select ActiveCell.FormulaR1C1 = "" End If Next Range("A3").Select End Sub New data is then entered into sheet ("Summary") and transferred to Sheet "(All_Data") which will be the start of a new (Ax:Dx).autofill 'also, is it always Ax:Dx? Yep. Set rngFill = wksPaste.Range("A" & lngStartRow, "D" & lngStartRow) 'how do you get the starting range for the .AutoFill destination? Set rngFillDst = Union((Range"A65536").End(xlUp).Offset(1, 0).SelectrngFillDst)) Ie.. the first empty cell below the last filled cell in €śA€ť Much thanks, Leah aka el dee "Matthew Herbert" wrote: On Jul 23, 4:09 pm, el dee wrote: Thanks Matthew! As you can see, I am a still VBA amaturish but am working on it. Sorry about the ambiguous Range Changes. As per 'Which worksheet? -- The same worksheet, the final master worksheet "All_Data" The range changes are something like follows: Range("A2:D2").AutoFill Destination:=Range("A2:D11")--The start range and end range of cells may change from something like ("A2:D2").AutoFill Destination:=Range("A2:D11") to ("A12:D12").AutoFill Destination:=Range ("A12:D26") to ("A27:D27").AutoFill Destination:=Range ("A35:D46")... on so on. The end of the fill (ie "D") should be determined by the end of entries in row "E" and the number of rows autofilled is not a constant. Thanks in advance! "Matthew Herbert" wrote: On Jul 23, 2:55 pm, el dee <el wrote: Yikes, Help. I have a macro that Cuts and Pastes data from a form(really a worksheet) to another worksheet. The data from each form is appended to the data contained in a master worksheet. Columns "C:O" have multiple rows and are filled first in the paste. Columns "A:D" only have one row of corresponding data and need to autofill to the end of data in Column "E". The number of rows filled with the autofill change with every form. The macro below but I need "A:D" to fill to the end of data in column "E" for each form that is entered. Sub Summary_To_Data() ' ' Summary_To_Data Macro Sheets("Summary").Select Range("B5").Select Selection.Copy Sheets("All_Data").Select Range("A65536").End(xlUp).Offset(1, 0).Select ActiveSheet.Paste Sheets("Summary").Select Range("E5").Select Application.CutCopyMode = False Selection.Copy Sheets("All_Data").Select Range("B65536").End(xlUp).Offset(1, 0).Select ActiveSheet.Paste Sheets("Summary").Select Range("I5").Select Application.CutCopyMode = False Selection.Copy Sheets("All_Data").Select Range("C65536").End(xlUp).Offset(1, 0).Select ActiveSheet.Paste Sheets("Summary").Select Range("I6").Select Application.CutCopyMode = False Selection.Copy Sheets("All_Data").Select Range("D65536").End(xlUp).Offset(1, 0).Select ActiveSheet.Paste Sheets("Summary").Select Range("A9:K9").Select Range(Selection, Selection.End(xlDown)).Select Application.CutCopyMode = False Selection.Copy Sheets("All_Data").Select Range("E65536").End(xlUp).Offset(1, 0).Select ActiveSheet.Paste Range("A2:D2").Select Application.CutCopyMode = False Selection.AutoFill Destination:=Range("A2:D11") <- HERE.. Range changes lots Range("A2:D11").Select End Sub Thanks el dee, I don't really follow your AutoFill comment. You'll need to be more specific than stating "Range changes"; how does the range change exactly? Also, which worksheet do you want to AutoFill? I include a somewhat simplified code block below. Best, Matthew Herbert Sub Summary_To_Data() Dim wksCopy As Worksheet Dim wksPaste As Worksheet Dim rngCopy As Range Dim rngPaste As Range With ThisWorkbook Set wksCopy = .Worksheets("Summary") Set wksPaste = .Worksheets("All_Data") End With Set rngCopy = SetCopyRange(wksCopy, "B5") Set rngPaste = SetPasteRangeByColumn(wksPaste, "A") rngCopy.Copy rngPaste Set rngCopy = SetCopyRange(wksCopy, "E5") Set rngPaste = SetPasteRangeByColumn(wksPaste, "B") rngCopy.Copy rngPaste Set rngCopy = SetCopyRange(wksCopy, "I5") Set rngPaste = SetPasteRangeByColumn(wksPaste, "C") rngCopy.Copy rngPaste Set rngCopy = SetCopyRange(wksCopy, "I6") Set rngPaste = SetPasteRangeByColumn(wksPaste, "D") rngCopy.Copy rngPaste Set rngCopy = SetCopyRange(wksCopy, "A9:K9").CurrentRegion Set rngPaste = SetPasteRangeByColumn(wksPaste, "E") rngCopy.Copy rngPaste 'Which worksheet? Range("A2:D2").AutoFill Destination:=Range("A2:D11") End Sub Function SetCopyRange(Wks As Worksheet, strAddress As String) As Range Set SetCopyRange = Wks.Range(strAddress) End Function Function SetPasteRangeByColumn(Wks As Worksheet, strColumn As String) As Range Dim lngRow As Long lngRow = Wks.Rows.Count Set SetPasteRangeByColumn = Wks.Cells(lngRow, strColumn).End (xlUp).Offset(1, 0) End Function- Hide quoted text - - Show quoted text - el dee, I'm not sure how you define your first range, i.e. the address to the left of the colon in the range preceeding .AutoFill, but getting the last range isn't too tough because you told me how. You can get the end of the entires in column "E" by still using the Function I provided; however, I'm not sure how you are getting the start of entries (hence why this will still be outstanding). Some additional code is added below with comments/questions. Best, Matt Dim rngFill As Range Dim rngFillDst As Range Dim lngStartRow As Long 'how do you determine the row for the .AutoFill range? lngStartRow = 2 'also, is it always Ax:Dx? Set rngFill = wksPaste.Range("A" & lngStartRow, "D" & lngStartRow) 'end of entries in E Offset -1 rows and -1 columns Set rngFillDst = SetPasteRangeByColumn(wksPaste, "E").Offset(-1, -1) 'how do you get the starting range for the .AutoFill destination? Set rngFillDst = Union(?StartOfTheRangeHere?, rngFillDst)- Hide quoted text - - Show quoted text - Leah, Your "ClearForm" can be greatly simplified to something like the following: With Sheets("Summary") .Range("B5,E5,I5,I6").Clear If .Range("A10").Value < "" Then .Range("A10:K" & MAX_ROW).Clear End If End With Your fill section can look something like the following: lngStartRow = SetPasteRangeByColumn(wksPaste, "A").Offset(-1, 0) Set rngFill = wksPaste.Range("A" & lngStartRow, "D" & lngStartRow) Set rngFillDst = SetPasteRangeByColumn(wksPaste, "E").Offset(-1, -1) Set rngFillDst = Union(rngFill.Cells(1), rngFillDst) rngFill.AutoFill rngFillDst Also, you'll want to take advantage of the debugging tools (Debug menu in VBE). Particuallarly, you'll want to step through your programs by hitting F8 repeatedly (Debug | Step Into). As the yellow line moves from one line to the next, you can place your cursor over variable names to see how the variable is behaving. With objects, you can print object properties to the Immediate Window (View | Immediate Window), among other things. For example, if you wanted to make sure that rngFill is the correct range you could insert a line of code in the procedure that will print the Address of the range object to the Immediate Window, i.e. Debug.Print rngFill.Address. .Address also has very useful parameters such as setting the relative reference of the Address and returning the External Address, e.g. Debug.Print rngFill.Address(External:=True). As you type a defined range object, such as rngFill, once you type "." you'll notice that the Intellisense window pops up with a number of properties and methods available to you. Once you select (via the arrow keys) or type a name, you can hit TAB to AutoComplete the name. Also, adding Breakpoints (Debug | Toggle Breakpoint - F9) will pause the code execution until you tell the program to continue running (Run | Continue - F5, or the Green Play button on the Standard Toolbar). Best, Matt |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Linking columns for auto fill | Excel Discussion (Misc queries) | |||
linking columns for auto fill-in | New Users to Excel | |||
Protecting and Auto fill Columns | Excel Worksheet Functions | |||
Auto fill of columns | Excel Discussion (Misc queries) | |||
auto fill columns | Excel Programming |