Results 1 to 5 of 5

Excel 2007 code to sort certain cells depending on current active cell

Threaded View

  1. #1
    Registered User
    Join Date
    05-26-2011
    Location
    48067
    MS-Off Ver
    Excel 2007
    Posts
    12

    Excel 2007 code to sort certain cells depending on current active cell

    Hey All, I've been scouring around the forums and haven't been able to come up with a solid answer to this: I'm trying to sort a section of data by a date column. The section to be sorted depends on where the user is entering information. For example the user would press a button to open a form and specify what month they are entering data for. The script finds that month, adds a new line of data, and then I want it to automatically sort the data for February by date.

    here's my code so far thats giving me problems, the Set Top and Set Bottom parts is where it hangs. Is there a better way to do this? (the Sorting part I got by recording a macro and changing the values to the variables i set)
    Private Sub cbAdd_Click()
    
    Dim Top
    Dim Bottom
    
    ActiveCell.Offset(0, -1).Select
    
    Set Top = Range(ActiveCell)	'selects the top of the current range to be sorted
    Set Bottom = Range(ActiveCell.End(xlDown)) 'selects the bottom cell of the range to be sorted
    
        Range(Top, Bottom).Select
        ActiveWorkbook.Worksheets("Daily 2011").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Daily 2011").Sort.SortFields.Add Key:=Range(Top) _
            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("Daily 2011").Sort
            .SetRange Range(Top, Bottom)
            .Header = xlGuess
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End If
    End Sub
    Last edited by mpalm; 05-26-2011 at 10:28 PM.

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