Results 1 to 2 of 2

Can I apply a macro that sorts within individual cells to a selection of cells at once?

Threaded View

Colleen1213 Can I apply a macro that... 10-12-2015, 02:27 PM
protonLeah Re: Can I apply a macro that... 10-12-2015, 02:41 PM
  1. #1
    Registered User
    Join Date
    10-12-2015
    Location
    Denver, CO
    MS-Off Ver
    2010
    Posts
    8

    Can I apply a macro that sorts within individual cells to a selection of cells at once?

    Hello!
    I'm pretty new to VBA so please excuse me. I am working with an established macro to sort data within a cell. However, I currently have to select the cell and run the macro on about 200 cells individually, which is very time consuming. Is there a way to modify this code (or use a completely different one) so that I can just highlight all of the cells and apply the sort macro once to all highlighed cells? Any help or suggestions are much appreciated! My existing code is below.
    Thank you all



    Option Explicit
    
    Public Sub SortVals()
        Dim i As Integer
        Dim arr As Variant
        arr = Split(ActiveCell.Text, ",")
    
        ' trim values so sort will work properly
        For i = LBound(arr) To UBound(arr)
            arr(i) = Trim(arr(i))
        Next i
    
        ' sort
        QuickSort arr, LBound(arr), UBound(arr)
    
        ' load sorted values back to cell
        Dim comma As String
        comma = ""
        ActiveCell = ""
        For i = LBound(arr) To UBound(arr)
            ActiveCell = ActiveCell & comma & CStr(arr(i))
            comma = ","
        Next i
    End Sub
    
    Public Sub QuickSort(vArray As Variant, inLow As Long, inHi As Long)
    
      Dim pivot   As Variant
      Dim tmpSwap As Variant
      Dim tmpLow  As Long
      Dim tmpHi   As Long
    
      tmpLow = inLow
      tmpHi = inHi
    
      pivot = vArray((inLow + inHi) \ 2)
    
      While (tmpLow <= tmpHi)
    
         While (vArray(tmpLow) < pivot And tmpLow < inHi)
            tmpLow = tmpLow + 1
         Wend
    
         While (pivot < vArray(tmpHi) And tmpHi > inLow)
            tmpHi = tmpHi - 1
         Wend
    
         If (tmpLow <= tmpHi) Then
            tmpSwap = vArray(tmpLow)
            vArray(tmpLow) = vArray(tmpHi)
            vArray(tmpHi) = tmpSwap
            tmpLow = tmpLow + 1
            tmpHi = tmpHi - 1
         End If
    
      Wend
    
      If (inLow < tmpHi) Then QuickSort vArray, inLow, tmpHi
      If (tmpLow < inHi) Then QuickSort vArray, tmpLow, inHi
    
    End Sub
    Last edited by Colleen1213; 10-12-2015 at 03:38 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Apply function to individual cells if it has value #N/A
    By VKu in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-04-2015, 07:35 PM
  2. VBA macro to apply formula for selected cells and enter result below selection
    By Petrachabrova in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-23-2014, 07:39 AM
  3. Fastest way to apply macro to all cells
    By dnnrobert in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-01-2014, 10:21 AM
  4. Replies: 3
    Last Post: 01-23-2012, 03:55 AM
  5. Linking Cells so sorts of one columb sorts another
    By excellerator22 in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 04-12-2008, 11:26 PM
  6. Replies: 2
    Last Post: 05-07-2007, 09:01 AM
  7. Macro to select cells and then new selection from selected cells
    By Rewop Eilsel in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-06-2006, 11:25 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1