Results 1 to 4 of 4

sort five columns - individually

Threaded View

  1. #1
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,669

    sort five columns - individually

    VBA gurus,
    By being so active here, I am learning some VBA coding. Mostly by copying and modifying code to suit my needs.
    I needed to figure out a way to:
    1. Look at five columns [each is filled to different size by another macro],
    and if row 1 in the respective column has text in it,
    2. Find last used row in that column and
    3. Sort each column alphabetically from Row 4 to end of that column.

    In the attached example I have a macro "sort".
    It works, but as you can see, I am using a lot of workarounds.
    In hopes of learning proper syntax; to loop though columns B-F and selecting appropriate range and then sorting the range; I am asking someone to look at the code and streamline it so its more efficient.

    Here's the code:
    Sub sort()
    Dim lastrow As Long
    Dim col As Integer
    Dim begin, last As String
    Dim Len1 As Integer
    Dim col1 As String
    Dim myrange As String
    For col = 2 To 6
    If col = 2 Then
    col1 = "B"
    End If
    If col = 3 Then
    col1 = "C"
    End If
    If col = 4 Then
    col1 = "D"
    End If
    If col = 5 Then
    col1 = "E"
    End If
    If col = 6 Then
    col1 = "F"
    End If
    
    
     Len1 = Len(ActiveSheet.Cells(1, col))
     
            If Len1 > 1 Then
    
            lastrow = ActiveSheet.Cells(Rows.Count, col).End(xlUp).Row
            begin = col1 & "4"
            last = col1 & lastrow
            myrange = begin & ":" & last
            ActiveSheet.Cells("4", col).Select
                Application.CutCopyMode = False
                 Range(myrange).Select
             Selection.sort Key1:=Range(begin), Order1:=xlAscending, Header:=xlNo, _
                OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
                    DataOption1:=xlSortNormal
            End If
    Next col
    
    End Sub




    Thank you for you help.
    modytrane.
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

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

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