Forum Discussion

presdetois's avatar
presdetois
Copper Contributor
Jun 28, 2023

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: 

2345678910111213141516
345678910111213141516 
234578910111213141516 
345678910111213141516 
135678910111213141516 
13456789101113141516 

 But, when i run the code I get this result: 

12345678910111213141516
2345678910111213141516 
1345678910111213141516 
1345678910111213141516 
1234578910111213141516 
2345678910111213141516 
1235678910111213141516 
123456789101113141516

 

12 Replies

  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor

    presdetois 

    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:

    1. Clearing the contents of rows 2 to the last row in the first sheet before splitting rows.
    2. 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.
    3. 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.

    • presdetois's avatar
      presdetois
      Copper 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.

      • NikolinoDE's avatar
        NikolinoDE
        Gold Contributor

        presdetois 

        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.

Resources