#Excel split cells copying values plus

The goal is to split the data in column C so that each product occupies a new row in the spreadsheet. Column C contains a list of products in that order, with each product separated by a semicolon.

Each order occupies one row in the worksheet. In Excel 95 or lower, you would have to change '1048576' to '16384', and in Excel 97-2003, to '65536'.The member who asked the question receives a new file every day that contains a list of orders that came in through the website. the 3rd column in the range), the call could look something like this: SplitRows Range('F6:H1048576'), 2, vbLf, 3 If there was no certainty about the number of rows, but we knew that all the valid rows are contiguous and always have a value in column H (i.e.

#Excel split cells copying values plus

If the table contained the row header plus 10 rows of data (instead of 1), and if it started in F5 again, the call would look like this: SplitRows Range('F6:H15'), 2, vbLf the data that would fall in column B if the table started in A1) was separated by Alt-Enters instead of commas, the call would look like this: SplitRows Range('F6:H6'), 2, vbLf If the same table started in F5 instead of A1, and if the data in column G (i.e. The function returns True if no errors were encountered, and False otherwise.ДЇor the range illustrated in the original question, the call would look like this: SplitRows Range('A2:C2'), 2, ',' The range object passed in the first argument will be modified by the function to reflect the range of all the new data rows (including all inserted rows). the optional (relative) number of the 'ID' column within the range (if a number >=1 is provided, the first row with no data in this column will be taken as the last row of data).the (relative) number of the column within the range with the string to split.

the range containing the rows of data (excluding the header).To use the above function, you would specify If Application.Calculation oldCal Then Application.Calculation = oldCal If Application.ScreenUpdating oldUpd Then Application.ScreenUpdating = oldUpd 'Loop through the data rows to split them as neededДo While r Then Set dataRng = dataRng.Resize(rowCount) Row + 1Дim splitLb As Long, splitUb As Long, splitI As Long Offset(, idCol - 1).Resize(, 1).End(xlDown).Row. 'If an ID column is specified, use it to determine where the table ends by finding the first row 'Modify application settings for the sake of speedДim rowCount As Long: rowCount = Optional ByVal idCol As Long = 0) As BooleanДim oldUpd As Variant: oldUpd = Application.ScreenUpdatingДim oldCal As Variant: oldCal = Application.Calculation Function SplitRows(ByRef dataRng As Range, ByVal splitCol As Long, ByVal splitSep As String, _ This function happens to have similarities to procedure, but it does not rely on the clipboard. It also avoids copying and inserting entire rows, and it allows you to specify a separator other than a comma. This method makes sure not to modify the cells to the left, to the right, or above the table of data, in case the table does not start in A1 or in case there is other data on the sheet besides the table. Given excellent and brief answer, the VBA function below might be a bit of an overkill, but it will hopefully be of some help to someone looking for a more 'generic' solution. Ws.Cells(iRow, ANALYSIS_ROW).Offset(iIndex).Value2 = iSplit(iIndex)ДЄpplication.Calculation = xlCalculationAutomatic ISize = UBound(iSplit) - LBound(iSplit) + 1ДЇor iIndex = LBound(iSplit) To UBound(iSplit)

ISplit = Split(ws.Cells(iRow, ANALYSIS_ROW).Value2, ',') Cells(.Rows.Count, ANALYSIS_ROW).End(xlUp).RowДЇor iRow = lastrow To DATA_START_ROW Step -1 Worksheets('Sheet4').Copy After:=.Worksheets('Sheet4') Option ExplicitДЄpplication.Calculation = xlCalculationManual