0

I'm coding a larger project that will automatically sort a table ("Table2") by the values in different columns, but I want to create an exception where rows with the same value in column D are always kept next to each other. There's definitely multiple issues with what I have written but it's a starting point. Rows Dim grpRange As String: grpRange = "A"&first&":J"&last and Rows(first:last).Group are currently showing up as red and when run, I'm getting a compile error the first time Rows(firstPos).Insert tries to run, and I'm getting an error that the program is running out of stack space.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("D3:D2000")) Is Nothing Then
        With Sheets("Sheet1")
            Dim wo As String
            Dim tb2 As ListObject
            Set tb2 = .ListObjects("Table2")
            Dim dupes() As Variant
            Dim t As Integer: t = 0
            Dim count As Integer: c = 2
            Dim newPos As Integer
            Dim Occurrences As Integer
            For Each rw In tb2.DataBodyRange.Rows    ' Iterate through each element.
                count = count + 1
                If rw.Cells(4).Value = ActiveCell.Value Then
                    If Occurrences = 0 Then
                        Occurrences = Occurrences + 1
                        Dim firstPos As Integer: firstPos = rw.Cells(4).Row
                    Else
                        Occurrences = Occurrences + 1
                        Rows(count).Cut
                        Rows(firstPos).Insert
                        newPos = firstPos - 1
                        ReDim dupes(0 To t)
                        dupes(t) = Rows(newPos).Row
                        t = t + 1
                    End If
                End If

                If Occurrences > 1 Then
                    Dim first As Integer: first = dupes(0)
                    Dim last As Integer: last = dupes(t)
                    Dim grpRange As String: grpRange = "A"&first&":J"&last
                    Rows(first:last).Group
                End If
            Next
        End With
    End If
End Sub

Is trying to group the rows like this even the right approach?

EDIT: Th reason why I'm not just sorting by Column D is that what the end user wants is the table to be auto-sorted by column c as primary and column e as secondary (this code is already implemented and works fine), however sometimes there are multiple rows with the same values in column D (for context it's the order #) but different values in Column E and he wants those kept together. The problem with just sorting col C>col D>col E is that the sorting in col E would then be totally messed up for all of the entries with no duplicates in column D.

9
  • 3
    The simplest way to ensure that rows with the same value in column D remain grouped together is to sort the table with column D as the primary / first key. Commented Nov 19, 2024 at 22:33
  • 2
    FYI ActiveCell could be anywhere on the worksheet so I would not use that for anything... Commented Nov 19, 2024 at 22:43
  • For syntax errors (red line): 1.String concatenation operator needs separated from stirng with spaces (before, after) in VBA. 2. Rows(first & ":" & last).Group Commented Nov 20, 2024 at 8:20
  • Guess that the c variable should be count here: Dim count As Integer: c = 2. At the moment your first use of count sets it to 1: count = count + 1 Commented Nov 20, 2024 at 13:43
  • Thanks for the syntax help everyone! @taller that's what I tried at first, but the problem is that what the end user wants is the table to be auto-sorted by column c as primary and column e as secondary (this code is already implemented and works fine), however sometimes there are multiple rows with the same values in column D (for context it's the order #) but different values in Column E and he wants those kept together. The problem with just sorting col C>col D>col E is that the sorting in col E would then be totally messed up for all of the entries with no duplicates in column D. Commented Nov 20, 2024 at 16:58

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.