[color=blue]Option[/color] [color=blue]Explicit[/color]<o:p></o:p>[/FONT][/COLOR]
[COLOR=gray][FONT=Verdana][color=blue]Sub[/color] BringAndMergatame()<o:p></o:p>[/FONT][/COLOR]
[COLOR=gray][FONT=Verdana][color=lightgreen]' 1) Get Initial Data for Output to Bookings.xls[/color]<o:p></o:p>[/FONT][/COLOR]
[COLOR=gray][FONT=Verdana][color=blue]Dim[/color] initworksheet [color=blue]As[/color] Worksheet [color=lightgreen]'Give Abbreviation Methods, Properties of Object Worksheet through .dot[/color]<o:p></o:p>[/FONT][/COLOR]
[COLOR=gray][FONT=Verdana][color=blue]Set[/color] initworksheet = ActiveWorkbook.Sheets("MergedData") [color=lightgreen]'Assumes Workbook1 is open and Active[/color]<o:p></o:p>[/FONT][/COLOR]
[COLOR=gray][FONT=Verdana][color=blue]Dim[/color] lmr [color=blue]As[/color] [color=blue]Long[/color], lmc [color=blue]As[/color] [color=blue]Long[/color] [color=lightgreen]'Variable for Rows, Columns, last Row last Column of merged sheet. Assume our File for Input has a reasonably well defined end. Long is a Big whole Number limit (-2,147,483,648 to 2,147,483,647) If you need some sort of validation the value should only be within the range of a Byte/Integer otherwise there's no point using anything but Long.--upon/after 32-bit, Integers (Short) need converted internally anyways, so a Long is actually faster.[/color]<o:p></o:p>[/FONT][/COLOR]
[COLOR=gray][FONT=Verdana][color=blue]Let[/color] lmc = 12: [color=blue]Let[/color] lmr = initworksheet.Cells.Find(What:="*", After:=initworksheet.Cells(1, 1), Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row [color=lightgreen]'Get last Row with entry anywhere for Sheet1. Method: You start at first cell then go backwards (which effectively starts at end of sheet), searching for anything ( = * ) by rows, then get the row number. This allows for different excel versions with different available Row numbers) Just a different method that finds last row in sheet rather than row for last entry in particular cell. Better to use that here as we are not sure which columns are full[/color]<o:p></o:p>[/FONT][/COLOR]
[COLOR=gray][FONT=Verdana][color=blue]Dim[/color] arrin() [color=blue]As[/color] Variant: [color=blue]Let[/color] arrin() = initworksheet.Range(initworksheet.Cells(2, 1), initworksheet.Cells(lmr, lmc)).Value [color=lightgreen]'Allowed VBA One Liner - An Array of variants may be set to a collection of Range values. The Range object works as to return a collection of (Variants initially) of various types. So Initially must see an Array of Variant types for compatability[/color]<o:p></o:p>[/FONT][/COLOR]
[COLOR=gray][FONT=Verdana]<o:p> </o:p>[/FONT][/COLOR]
[COLOR=gray][FONT=Verdana][color=lightgreen]' 2) Obtain a I dimensional "pseudo" horizontal Array of LookUpColumn[/color]<o:p></o:p>[/FONT][/COLOR]
[COLOR=gray][FONT=Verdana][color=blue]Dim[/color] vLkUpc [color=blue]As[/color] Long: [color=blue]Let[/color] vLkUpc = 12 [color=lightgreen]'set column number 'Column where search criteria for filtering is. '( Long is a Big whole Number limit (-2,147,483,648 to 2,147,483,647) If you need some sort of validation the value should only be within the range of a Byte/Integer otherwise there's no point using anything but Long.--upon/after 32-bit, Integers (Short) need converted internally anyways, so a Long is actually faster.[/color]<o:p></o:p>[/FONT][/COLOR]
[COLOR=gray][FONT=Verdana] [color=blue]Dim[/color] x() [color=blue]As[/color] [color=blue]Variant[/color] [color=lightgreen]'Dynamic one dimensional array for lookUpColumn[/color]<o:p></o:p>[/FONT][/COLOR]
[COLOR=gray][FONT=Verdana] [color=blue]Let[/color] x() = Application.WorksheetFunction.Index(arrin(), 0, vLkUpc) [color=lightgreen]'Returns format type (1,1) (2,1) (3,1) (4,1) >> Index Function with second argument (row co - ordinate) set to 0 will return the entire row given by first argument ( row - co ordinate ), applied to the first argument which is the grid, ( Array , Row_Number, Column_Number) http://www.excelforum.com/excel-new-users-basics/1080634-vba-1-dimensional-horizontal-and-vertical-array-conventions-ha-1-2-3-4-a.html[/color]<o:p></o:p>[/FONT][/COLOR]
[COLOR=gray][FONT=Verdana] [color=blue]Let[/color] x() = Application.WorksheetFunction.Transpose(x()) [color=lightgreen]'working on 2 dimensional array of one column, conveniently by convenience returns 'Returns format type (1) (2) (3) (4) , a one dimension "psuedo" horizontal Array[/color]<o:p></o:p>[/FONT][/COLOR]
[COLOR=gray][FONT=Verdana]<o:p> </o:p>[/FONT][/COLOR]
[COLOR=gray][FONT=Verdana][color=lightgreen]' 3) Obtain an Array of indicies for rows meeting criteria for sort, and all colimn Indicies.[/color]<o:p></o:p>[/FONT][/COLOR]
[COLOR=gray][FONT=Verdana][color=blue]Dim[/color] strRows [color=blue]As[/color] [color=blue]String[/color] [color=lightgreen]'Used as Temporary concatenated string fo row indicies[/color]<o:p></o:p>[/FONT][/COLOR]
[COLOR=gray][FONT=Verdana] [color=blue]Dim[/color] iii [color=blue]As[/color] [color=blue]Long[/color] [color=lightgreen]'Loop Bound (Count) variable[/color]<o:p></o:p>[/FONT][/COLOR]
[COLOR=gray][FONT=Verdana] [color=blue]For[/color] iii = [color=blue]LBound[/color](x()) [color=blue]To[/color] [color=blue]UBound[/color](x()) [color=lightgreen]'Lower Bound by me is start of data[/color]<o:p></o:p>[/FONT][/COLOR]
[COLOR=gray][FONT=Verdana] [color=blue]If[/color] x(iii) >= 0.01 [color=blue]Then[/color] strRows = strRows & " " & iii [color=lightgreen]'Concatenating valid "row" indicies[/color]<o:p></o:p>[/FONT][/COLOR]
[COLOR=gray][FONT=Verdana] [color=blue]Next[/color] iii<o:p></o:p>[/FONT][/COLOR]
[COLOR=gray][FONT=Verdana] [color=blue]Let[/color] strRows = Trim(strRows) [color=lightgreen]'trim off first space[/color]<o:p></o:p>[/FONT][/COLOR]
[COLOR=gray][FONT=Verdana] [color=blue]Dim[/color] rws() [color=blue]As[/color] String: [color=blue]Let[/color] rws() = Split(strRows, " ") [color=lightgreen]'Finally rws becomes a 1 dimension1 "Psuedo" horizontal Array of the selected row inicia. The space is default, so " " could be ommited.. Split is a strings function usually returnig a String - Dim rws as variant would work , but not Dim rws() as variant From Post 48 http://www.excelforum.com/excel-new-users-basics/1058406-range-dimensioning-range-and-value-referencing-and-referring-to-arrays-4.html[/color]<o:p></o:p>[/FONT][/COLOR]
[COLOR=gray][FONT=Verdana] [color=blue]Let[/color] strRows = "" [color=lightgreen]'Finished with using temporary concatenated indicie values so empty it ready for next looping[/color]<o:p></o:p>[/FONT][/COLOR]
[COLOR=gray][FONT=Verdana] [color=blue]Dim[/color] clms() [color=blue]As[/color] Variant: [color=blue]Let[/color] clms() = Evaluate("row(1:" & [color=blue]UBound[/color](arrin(), 2) & ")") [color=lightgreen]' 'Returns 1 column 2 dimensional array of size _: to :_ In that array are the number _: to :_[/color]<o:p></o:p>[/FONT][/COLOR]
[COLOR=gray][FONT=Verdana][color=lightgreen]' 4 ) Obtain Full Column Output Array based on "row" selection criteria[/color]<o:p></o:p>[/FONT][/COLOR]
[COLOR=gray][FONT=Verdana] [color=blue]Dim[/color] y() [color=blue]As[/color] [color=blue]Variant[/color] [color=lightgreen]'I believe here the Index is working in some "vector" type form here. VBA "works" as follows here:[/color]<o:p></o:p>[/FONT][/COLOR]
[COLOR=gray][FONT=Verdana] [color=blue]Let[/color] y() = Application.Index(arrin(), rws(), clms()) [color=lightgreen]'It takes in turn each of the indicies in rws()[/color]<o:p></o:p>[/FONT][/COLOR]
[COLOR=gray][FONT=Verdana] [color=lightgreen]'and for each of these it steps through the indicies in clms(). It returns then effectively a "column" of values.[/color]<o:p></o:p>[/FONT][/COLOR]
[COLOR=gray][FONT=Verdana] [color=lightgreen]' These values are then the entities in the main Array arrin() given by those co-ordinates. In our case then, we initially put into the new Array y(), a column which contains the first data row.[/color]<o:p></o:p>[/FONT][/COLOR]
[COLOR=gray][FONT=Verdana] [color=blue]Let[/color] y() = Application.WorksheetFunction.Transpose(y()) [color=lightgreen]'...As this process is then repeated for all the indicies given in rws() we effectively have an Array y() of our required output rows , but transposed. ( so we transpose it back to the correct orientation! )[/color]<o:p></o:p>[/FONT][/COLOR]
[COLOR=gray][FONT=Verdana][color=lightgreen]' 5 )obtain an Array of indicies for required output columns and all "row" indices[/color]<o:p></o:p>[/FONT][/COLOR]
[COLOR=gray][FONT=Verdana] [color=blue]Dim[/color] c() [color=blue]As[/color] Variant: [color=blue]Let[/color] c() = Array("2", "12"): [color=blue]Let[/color] c() = Application.WorksheetFunction.Transpose(c())<o:p></o:p>[/FONT][/COLOR]
[COLOR=gray][FONT=Verdana] [color=blue]For[/color] iii = [color=blue]LBound[/color](y(), 1) [color=blue]To[/color] [color=blue]UBound[/color](y(), 1) [color=lightgreen]'Lower Bound by me is start of data[/color]<o:p></o:p>[/FONT][/COLOR]
[COLOR=gray][FONT=Verdana] strRows = strRows & " " & iii [color=lightgreen]'Concatenating valid "row" indicies[/color]<o:p></o:p>[/FONT][/COLOR]
[COLOR=gray][FONT=Verdana] [color=blue]Next[/color] iii<o:p></o:p>[/FONT][/COLOR]
[COLOR=gray][FONT=Verdana] [color=blue]Let[/color] strRows = Trim(strRows) [color=lightgreen]'trim off first space[/color]<o:p></o:p>[/FONT][/COLOR]
[COLOR=gray][FONT=Verdana] [color=blue]Dim[/color] r() [color=blue]As[/color] String: [color=blue]Let[/color] r() = Split(strRows, " ")<o:p></o:p>[/FONT][/COLOR]
[COLOR=gray][FONT=Verdana][color=lightgreen]' 6 ) Obtain output Array with required Columns[/color]<o:p></o:p>[/FONT][/COLOR]
[COLOR=gray][FONT=Verdana] [color=blue]Dim[/color] outArr() [color=blue]As[/color] Variant: [color=blue]Let[/color] outArr() = Application.WorksheetFunction.Transpose(Application.Index(y(), r(), c())) [color=lightgreen]'Same procedure as for getting Y() but in one SHimpfGlified[/color]<o:p></o:p>[/FONT][/COLOR]
[COLOR=gray][FONT=Verdana]<o:p> </o:p>[/FONT][/COLOR]
[COLOR=gray][FONT=Verdana][color=lightgreen]' 7 ) Open Bookungs Workbook[/color]<o:p></o:p>[/FONT][/COLOR]
[COLOR=gray][FONT=Verdana][color=blue]Dim[/color] myData [color=blue]As[/color] Workbook [color=lightgreen]'Give Abbreviation Methods, Properties of Object Workbook through .dot[/color]<o:p></o:p>[/FONT][/COLOR]
[COLOR=gray][FONT=Verdana][color=blue]Set[/color] myData = Workbooks.Open(ActiveWorkbook.Path & "\Bookings.xls") [color=lightgreen]'Workbooks.Open returns a Workbook object so we can imediately set our variable for this workbook to it in one neat line[/color]<o:p></o:p>[/FONT][/COLOR]
[COLOR=gray][FONT=Verdana][color=blue]Dim[/color] destworksheet [color=blue]As[/color] Worksheet: [color=blue]Set[/color] destworksheet = myData.Sheets("Sheet1") [color=lightgreen]'Shheet for output[/color]<o:p></o:p>[/FONT][/COLOR]
[COLOR=gray][FONT=Verdana][color=lightgreen]' 8 ) Output results[/color]<o:p></o:p>[/FONT][/COLOR]
[COLOR=gray][FONT=Verdana][color=blue]Let[/color] destworksheet.Range("A9").Resize(UBound(outArr(), 1), UBound(outArr(), 2)).Value = outArr() [color=lightgreen]'A nice "One" liner - Resize selected cell to size of output Array and then the allowed VBA assignment of a collection of values to a Spreadsheet range[/color]<o:p></o:p>[/FONT][/COLOR]
[COLOR=gray][FONT=Verdana][color=lightgreen]'Save and close workbook[/color]<o:p></o:p>[/FONT][/COLOR]
[COLOR=gray][FONT=Verdana][color=lightgreen]'myData.Save'Save only[/color]<o:p></o:p>[/FONT][/COLOR]
[COLOR=gray][FONT=Verdana]myData.Close (1)<o:p></o:p>[/FONT][/COLOR]
[COLOR=gray][FONT=Verdana]<o:p> </o:p>[/FONT][/COLOR]
[COLOR=gray][FONT=Verdana][color=blue]End[/color] [color=blue]Sub[/color] [color=lightgreen]'BringAndMergatame()[/color]<o:p></o:p>[/FONT][/COLOR]
[COLOR=gray][FONT=Verdana][color=lightgreen]'[/color]
></o
