CODEWITHSUNDEEP
vbaexcel
Mingebag
Mingebag

Reputation: 758

Grouping Excel 1004 Error

What I am trying to do: I have a excel sheet with 9 columns. In the excel sheet to be grouped by column A

What I did so far 

Public Sub GroupCells()
    Dim myRange As Range
    Dim rowCount As Integer, currentRow As Integer
    Dim firstBlankRow As Integer, lastBlankRow As Integer
    Dim currentRowValue As String
    Dim neighborColumnValue As String


    Set myRange = Range("A1:A1000")
    rowCount = Cells(Rows.Count, myRange.Column).End(xlUp).Row

    firstBlankRow = 0
    lastBlankRow = 0
    'for every row in the range
    For currentRow = 1 To rowCount
        currentRowValue = Cells(currentRow, myRange.Column).Value
        neighborColumnValue = Cells(currentRow, myRange.Column - 1).Value

        If (IsEmpty(currentRowValue) Or currentRowValue = "") Then
            'if cell is blank and firstBlankRow hasn't been assigned yet
            If firstBlankRow = 0 Then
                firstBlankRow = currentRow
            End If
        ElseIf Not (IsEmpty(currentRowValue) Or currentRowValue = "") Then
            'if the cell is not blank and its neighbor's (to the left) value is 0,
            'and firstBlankRow hasn't been assigned, then this is the firstBlankRow
            'to consider for grouping
            If neighborColumnValue = 0 And firstBlankRow = 0 Then
                firstBlankRow = currentRow
            ElseIf neighborColumnValue <> 0 And firstBlankRow <> 0 Then
                'if firstBlankRow is assigned and this row has a value with a neighbor
                'who isn't 0, then the cell one row above this one is to be considered
                'the lastBlankRow to include in the grouping
                lastBlankRow = currentRow - 1
            End If
        End If

        'if first AND last blank rows have been assigned, then create a group
        'then reset the first/lastBlankRow values to 0 and begin searching for next
        'grouping
        If firstBlankRow <> 0 And lastBlankRow <> 0 Then
            Range(Cells(firstBlankRow, myRange.Column), Cells(lastBlankRow, myRange.Column)).EntireRow.Select
            Selection.Group
            firstBlankRow = 0
            lastBlankRow = 0
        End If
    Next
End Sub

For this solution i am using the code of a fallow stackoverflow /co programmer called "Sam" enter image description here I go the error 1004 when i exceute my macro in excel

Upvotes: 0

Views: 137

Answers (1)

AmyOakes
AmyOakes

Reputation: 61

Try remving the minus 1 from neighborColumnValue you've set your column to A via myRange You cant take a column away from A 

neighborColumnValue = Cells(currentRow, myRange.Column - 1).Value

Becomes 

neighborColumnValue = Cells(currentRow, myRange.Column).Value

Upvotes: 1

Related Questions