Microsoft Office Forums

Go Back   Microsoft Office Forums > >

 
 
Thread Tools Display Modes
Prev Previous Post   Next Post Next
  #8  
Old 05-02-2022, 08:24 AM
p45cal's Avatar
p45cal p45cal is offline Macro or VBA to sort values Windows 10 Macro or VBA to sort values Office 2019
Expert
 
Join Date: Apr 2014
Posts: 956
p45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond repute
Default

Also at https://www.msofficeforums.com/excel...rt-values.html

Code:
Sub blah()
StartChars = Array("AB", "BC", "CD", "DE") 'what you're looking for
Set Destn = Range("D2")    'top left of area where results will go.
'Find range to process:
Set Rng = Range("B6").End(xlDown)
If IsEmpty(Rng.Value) Then
  Set Rng = Range("B6")
Else
  Set Rng = Range(Range("B6"), Rng)
End If
'Create the results in-memory:
ReDim Results(1 To UBound(StartChars) - LBound(StartChars) + 1)
For Each cll In Rng.Cells
  colm = Application.Match(Left(Application.Trim(cll.Value), 2), StartChars, 0)
  If Not IsError(colm) Then
    If IsEmpty(Results(colm)) Then Set Results(colm) = CreateObject("Scripting.Dictionary")
    Results(colm).Add cll.Value & Rnd, cll.Value
  End If
Next cll
colm = 0
'determine size of area to clear for the results:
maxRows = 0
For Each result In Results
  maxRows = Application.Max(result.Count, maxRows)
Next result
'clear that area (+1 blank row):
Destn.Resize(maxRows + 1, UBound(Results)).Clear
For Each result In Results
  Destn.Offset(, colm).Resize(result.Count).Value = Application.Transpose(result.items)
  colm = colm + 1
Next result
End Sub

I suspect I'll give ntldr123 a wide berth in the future since he'll probably already have a solution elsewhere.
Reply With Quote
 



Similar Threads
Thread Thread Starter Forum Replies Last Post
custom sort macro ewso Excel Programming 7 10-10-2017 12:08 PM
Macro or VBA to sort values Macro to sort columns SerenityNetworks Excel Programming 4 09-02-2016 06:20 AM
Macro or VBA to sort values Selecting values from different rows in a table and sort them in order in a single row FromF Excel 2 09-09-2014 02:30 AM
Looking for Help to Create a Macro (Sort) rsrasc Word VBA 5 04-16-2014 03:25 AM
Sort table using macro saslotteroy Word VBA 2 09-15-2011 02:41 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 12:33 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft