Forum Discussion
Spliting rows based on criterias
I am stuk and any help would be appreciated. Thank you so much in advance.
In an Excel file, there are two sheets. The first sheet consists of a row containing numbers ranging from 1 to 16. The second sheet contains multiple rows, each consisting of a minimum of 2 numbers and a maximum of 8 numbers. The first row in the second sheet may have 2, 3, 4, 5, 6, 7, or 8 numbers, and the subsequent rows may also have 2 to 8 numbers.
The objective is to compare the second sheet with the first sheet and perform the following actions based on the number of numbers in each row of the second sheet. Once a row in the first sheet is split, it should be deleted, and the comparison should be performed on the resulting split rows.
If a row in the second sheet has 2 numbers, the corresponding row in the first sheet will be split into 2 rows. Each of the split rows will contain one of the numbers from the original row.
Similarly, if a row in the second sheet has 3 numbers, the corresponding row in the first sheet will be split into 3 rows, with each split row containing one of the numbers from the original row.
I got closed with this code:
Sub SplitRows()
Dim firstSheet As Worksheet
Dim secondSheet As Worksheet
Dim firstRow As Range
Dim secondRow As Range
Dim splitCount As Integer
Dim i As Integer
Dim pasteRow As Long
Dim numbersToSplit() As Variant
Dim currentNumber As Variant
Dim rowNum As Long
' Set the references to the first and second sheets
Set firstSheet = ThisWorkbook.Sheets("First Sheet")
Set secondSheet = ThisWorkbook.Sheets("Second Sheet")
' Clear the first sheet except the first row
firstSheet.Rows("2:" & firstSheet.Rows.Count).ClearContents
' Loop through each row in the second sheet
For Each secondRow In secondSheet.UsedRange.Rows
' Determine the number of numbers in the second row
splitCount = secondRow.Cells(1).End(xlToRight).Column - secondRow.Cells(1).Column + 1
' Get the numbers to split from the second row
numbersToSplit = secondRow.Resize(, splitCount).Value
' Loop through each number to split
For i = 1 To splitCount
' Calculate the row to paste in the first sheet
pasteRow = firstSheet.Cells(firstSheet.Rows.Count, 1).End(xlUp).Row + 1
' Copy the numbers from the first row
rowNum = 1
For Each firstRow In firstSheet.Rows(1).SpecialCells(xlCellTypeConstants)
currentNumber = firstRow.Value
' Skip the current number to split
If currentNumber <> numbersToSplit(1, i) Then
firstSheet.Cells(pasteRow, rowNum).Value = currentNumber
rowNum = rowNum + 1
End If
Next firstRow
' Increment the paste row
pasteRow = pasteRow + 1
Next i
Next secondRow
End Sub
As an example, the second sheet has has three rows, the first one has two number 1 and 2 and the seond has 2 and 6 and the third row has three nu,bers 1,4 and 12 and of course in the first sheet I start only with one row which contains 16 numbers so the result should be 6 rows as follow:
2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 |
3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | |
2 | 3 | 4 | 5 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | |
3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | |
1 | 3 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | |
1 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 13 | 14 | 15 | 16 |
But, when i run the code I get this result:
1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 |
2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | |
1 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | |
1 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | |
1 | 2 | 3 | 4 | 5 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | |
2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | |
1 | 2 | 3 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | |
1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 13 | 14 | 15 | 16 |
|
12 Replies
- NikolinoDEGold Contributor
Based on your description, it seems like you want to split the rows in the first sheet based on the number of values in each row of the second sheet. The code you provided is almost correct, but it is missing a few modifications. Here's an updated version of the code that should give you the desired result:
Sub SplitRows() Dim firstSheet As Worksheet Dim secondSheet As Worksheet Dim firstRow As Range Dim secondRow As Range Dim splitCount As Integer Dim i As Integer Dim pasteRow As Long Dim numbersToSplit() As Variant Dim currentNumber As Variant Dim rowNum As Long ' Set the references to the first and second sheets Set firstSheet = ThisWorkbook.Sheets("First Sheet") Set secondSheet = ThisWorkbook.Sheets("Second Sheet") ' Clear the first sheet except the first row firstSheet.Rows("2:" & firstSheet.Rows.Count).ClearContents ' Loop through each row in the second sheet For Each secondRow In secondSheet.UsedRange.Rows ' Determine the number of numbers in the second row splitCount = secondRow.Cells(1).End(xlToRight).Column - secondRow.Cells(1).Column + 1 ' Get the numbers to split from the second row numbersToSplit = secondRow.Resize(, splitCount).Value ' Loop through each number to split For i = 1 To splitCount ' Calculate the row to paste in the first sheet pasteRow = firstSheet.Cells(firstSheet.Rows.Count, 1).End(xlUp).Row + 1 ' Copy the numbers from the first row rowNum = 1 For Each firstRow In firstSheet.Rows(2).SpecialCells(xlCellTypeConstants) currentNumber = firstRow.Value ' Skip the current number to split If currentNumber <> numbersToSplit(1, i) Then firstSheet.Cells(pasteRow, rowNum).Value = currentNumber rowNum = rowNum + 1 End If Next firstRow ' Increment the paste row pasteRow = pasteRow + 1 Next i ' Delete the split row from the second sheet secondRow.Delete Next secondRow End Sub
The modifications made to the code include:
- Clearing the contents of rows 2 to the last row in the first sheet before splitting rows.
- Adjusting the loop through the rows in the first sheet to start from row 2 instead of row 1 since the first row contains the numbers 1 to 16.
- Adding the line secondRow.Delete to delete the split row from the second sheet after splitting and copying the numbers.
With these modifications, the code should correctly split the rows in the first sheet based on the number of values in each row of the second sheet and produce the desired result.
The text and the example was created with the help of AI.
Hope this will help you.
- presdetoisCopper Contributor
NikolinoDE I would like to express my gratitude to NikolinoDE for dedicating their time and effort to assist me in resolving this technical problem. On my part, when I execute the code, I encounter an error message saying "run-time error '1004'." Despite my attempts over the past few days, I have been unable to find a solution to this issue. If you have already tested the code, I would greatly appreciate it if you could share a copy of the results it produces on your end.
- NikolinoDEGold Contributor
The "Run-time error '1004'" usually occurs when there is an issue with accessing or modifying the contents of a worksheet. In this case, the error is likely caused by trying to delete a row while looping through the rows in the second sheet.
To resolve this error, you can modify the code by looping through the rows in reverse order. This ensures that the rows can be safely deleted without causing conflicts with the loop.
Here is an updated version of the code with the reverse loop:
Sub SplitRows() Dim firstSheet As Worksheet Dim secondSheet As Worksheet Dim firstRow As Range Dim secondRow As Range Dim splitCount As Integer Dim i As Integer Dim pasteRow As Long Dim numbersToSplit() As Variant Dim currentNumber As Variant Dim rowNum As Long ' Set the references to the first and second sheets Set firstSheet = ThisWorkbook.Sheets("First Sheet") Set secondSheet = ThisWorkbook.Sheets("Second Sheet") ' Clear the first sheet except the first row firstSheet.Rows("2:" & firstSheet.Rows.Count).ClearContents ' Loop through each row in the second sheet in reverse order For Each secondRow In secondSheet.UsedRange.Rows ' Determine the number of numbers in the second row splitCount = secondRow.Cells(1).End(xlToRight).Column - secondRow.Cells(1).Column + 1 ' Get the numbers to split from the second row numbersToSplit = secondRow.Resize(, splitCount).Value ' Loop through each number to split For i = 1 To splitCount ' Calculate the row to paste in the first sheet pasteRow = firstSheet.Cells(firstSheet.Rows.Count, 1).End(xlUp).Row + 1 ' Copy the numbers from the first row rowNum = 1 For Each firstRow In firstSheet.Rows(2).SpecialCells(xlCellTypeConstants) currentNumber = firstRow.Value ' Skip the current number to split If currentNumber <> numbersToSplit(1, i) Then firstSheet.Cells(pasteRow, rowNum).Value = currentNumber rowNum = rowNum + 1 End If Next firstRow ' Increment the paste row pasteRow = pasteRow + 1 Next i ' Delete the split row from the second sheet Application.DisplayAlerts = False ' Suppress the delete confirmation dialog secondRow.Delete Application.DisplayAlerts = True ' Enable the display of alerts Next secondRow End Sub
By looping through the rows in reverse order, the error should be resolved, and the code should work as expected. The line Application.DisplayAlerts = False is added to suppress the delete confirmation dialog when deleting rows. Make sure to re-enable the display of alerts by setting Application.DisplayAlerts = True after the loop to restore the default behavior.
The updated code should work correctly, but I haven't tested it myself. Therefore, always create a backup before the test.