nightmazino
New Member
- Joined
- Apr 8, 2020
- Messages
- 21
- Office Version
- 2013
- Platform
- Windows
So I have a script where I sort the table to group data by their Base Rate. But there are lines that are similar to each other so when you sort them, all the base rates lines stick together.
Here's a sample. This should be the correct sorting:
Base Rate first then all similar IDs that are not Base Rate is under the Base Rate line.
But the issue is this:
As you can see, since their ID are the same, when sorted, the Base Rate lines tend to stick together. Same with the other lines that are not base rates.
I already wrote a code on how to handle this but it's still inefficient since it takes a long time to finish. Here's the code:
Here's a sample. This should be the correct sorting:
Base Rate first then all similar IDs that are not Base Rate is under the Base Rate line.
But the issue is this:
As you can see, since their ID are the same, when sorted, the Base Rate lines tend to stick together. Same with the other lines that are not base rates.
I already wrote a code on how to handle this but it's still inefficient since it takes a long time to finish. Here's the code:
VBA Code:
Sub arrangeBaseRates(descCol As String, lastCol As String)
Dim srcWS As Worksheet: Set srcWS = ThisWorkbook.Sheets("Source")
Dim descHolder As String, currentDesc As String
srcLR = srcWS.Cells(Rows.Count, 1).End(xlUp).Row
For x = 2 To srcLR
If LCase(srcWS.Range(descCol & x).Value) = "base rate" And LCase(srcWS.Range(descCol & x + 1).Value) = "base rate" Then
For i = 2 To 40
currentDesc = LCase(srcWS.Range(descCol & x + i).Value)
If InStr(1, descHolder, currentDesc) = 0 And currentDesc <> "base rate" Then
descHolder = descHolder + LCase(srcWS.Range(descCol & x + i).Value)
srcWS.Rows(x + 1).Insert shift:=xlDown
srcWS.Range("A" & x + 1 & ":" & lastCol & x + 1).Value = srcWS.Range("A" & x + i + 1 & ":" & lastCol & x + i + 1).Value
srcWS.Rows(x + i + 1).Delete shift:=xlUp
ElseIf currentDesc = "base rate" And i > 5 Then
Exit For
End If
Next i
End If
descHolder = ""
Next x
End Sub