Sort a list by drag and drop using VBA event handlers

[kienleong]

Hello All,

I have built a sheet that allows the user to sort a table of data. Rather than using sort values, I am replicating a drag-and-drop with a single click to select the row and another to insert it at another place in the table.

The best way I can come up with uses worksheet_SelectionChange like so:

Global OldValue as Variant

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim LastCol As Long, LastRow As Long
    Dim NewRow As Long
    
    LastCol = Me.Cells(1, 1).End(xlToRight).Column
    LastRow = Me.Cells(1048576, 1).End(xlUp).Row
    
    If Target.Column <> LastCol Or _
        Target.Row > LastRow Then Exit Sub
    
    If Application.CutCopyMode = False Then
        OldValue = Target.Row
        Me.Range(Cells(OldValue, 1), Cells(OldValue, LastCol)).Copy
    Else
        NewRow = Target.Row
        If OldValue > NewRow Then OldValue = OldValue + 1
        Me.Cells(NewRow, 1).Insert shift:=xlDown
        Application.CutCopyMode = False
        Me.Rows(OldValue).Delete
    End If
  
End Sub

This works fine, but it uses Global variables. I understand they are not best practice and should be avoided if possible.

Is there any way to do this without using Global variables?

 

[VoG]

You could try this instead

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static OldValue As Variant

    Dim LastCol As Long, LastRow As Long
    Dim NewRow As Long
    
    LastCol = Me.Cells(1, 1).End(xlToRight).Column
    LastRow = Me.Cells(1048576, 1).End(xlUp).Row
    
    If Target.Column <> LastCol Or _
        Target.Row > LastRow Then Exit Sub
    
    If Application.CutCopyMode = False Then
        OldValue = Target.Row
        Me.Range(Cells(OldValue, 1), Cells(OldValue, LastCol)).Copy
    Else
        NewRow = Target.Row
        If OldValue > NewRow Then OldValue = OldValue + 1
        Me.Cells(NewRow, 1).Insert shift:=xlDown
        Application.CutCopyMode = False
        Me.Rows(OldValue).Delete
    End If
  
End Sub

 

[kienleong]

Ah, yes. Brilliant, it works perfectly.

Many thanks!