Consulting

Results 1 to 9 of 9

Thread: auto filter on adding rows

  1. #1
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,033
    Location

    auto filter on adding rows

    title should be dynamic sort on adding rows. Would like some sort of worksheet change event code.

    I think I have found this on here before but its been years.

    I will be adding rows to a workbook each week. I need them to automatically sort on columns A,C and D. Columns C and D have dates in them so I need to keep them in date order.

    total columns are A-E. It might have been Zack who helped me with this when he was still on frequently around 2004. I cant seem to locate anything close to what I want.
    Peace of mind is found in some of the strangest places.

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Would like some sort of worksheet change event code.
    I'd make it part of the Worksheet_Deactivate event since you might be adding a number of rows with multiple columns

    I don't think I'd like it activating each time there was ANY change


    total columns are A-E.
    You mean A and E? Not A through E?

    How are the totals calculated: one day, running, beginning to end, ...?
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,033
    Location
    Should have posted the WB. On the attached Sheet1 I need to add a weeks worth of rows like my name shows. The calculation on each row from start time to end time difference doesn't seem correct. Im not going to be running this so I need a way for the user to just paste the rows at the end of the spreadsheet and sort them in the correct order.
    Attached Files Attached Files
    Peace of mind is found in some of the strangest places.

  4. #4
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    I'd do something like this


    In standard module

    Option Explicit
    
    Sub SortData(ws As Worksheet)
    
        Dim r As Range, r1 As Range
        
        Application.ScreenUpdating = False
            
        With ws
            
            Set r = .Range("A6").CurrentRegion
            Set r1 = r.Cells(2, 1).Resize(r.Rows.Count - 1, r.Columns.Count)
        
            With .Sort
                .SortFields.Clear
                .SortFields.Add Key:=r1.Columns(1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
                .SortFields.Add Key:=r1.Columns(2), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
                .SortFields.Add Key:=r1.Columns(3), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
                .SortFields.Add Key:=r1.Columns(4), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
                .SetRange r
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
        
            Set r = .Range("A6").CurrentRegion
        End With
            
        Set r1 = r.Cells(2, 1).Resize(r.Rows.Count - 1, r.Columns.Count)
            
        With r1
            .HorizontalAlignment = xlCenter
            .Columns(3).NumberFormat = "m/d/yyyy h:mm"
            .Columns(4).NumberFormat = "m/d/yyyy h:mm"
            .Columns(5).Formula = "=IF(OR(C7=0,D7=0),"""",D7-C7)"
            .Columns(5).NumberFormat = "h:mm"
        End With
        Application.ScreenUpdating = False
    
    '    MsgBox "Sorted"
    
    End Sub



    In Sheet1 code module

    Private Sub Worksheet_Activate()
        Call SortData(Me)
    End Sub
    
    Private Sub Worksheet_Deactivate()
        Call SortData(Me)
    End Sub
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  5. #5
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,033
    Location
    Hi Paul

    Thanks for the help. when I paste the new rows at the bottom it doesnt sort it into alphabetical order. Am I missing something? Also is there a way to add two combo boxes to filter for a specific date range?
    Attached Files Attached Files
    Peace of mind is found in some of the strangest places.

  6. #6
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Quote Originally Posted by austenr View Post
    Hi Paul

    Thanks for the help. when I paste the new rows at the bottom it doesnt sort it into alphabetical order. Am I missing something? Also is there a way to add two combo boxes to filter for a specific date range?

    1. Did you exit and return to the data sheet? The way I have the sort is that it only runs when entering or exiting the worksheet, and only if EnableEvents = True

    From Post #4

    Private Sub Worksheet_Activate() 
        Call SortData(Me) 
    End Sub 
     
    Private Sub Worksheet_Deactivate() 
        Call SortData(Me) 
    End Sub
    2. I added Bill in row 22, went to Sheet2, and then back

    Capture.JPG

    3. The event can be changed to Worksheet_Change, but that seemed like too many firings
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  7. #7
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,033
    Location
    I see. I got that to work. something i do not understand is why the paste function is disabled on sheet1? The user will want to just past the new data into the sheet switch sheets and come back having it sorted. I tried to paste rows from sheet 2 to sheet 1 but the paste function was disabled???
    Peace of mind is found in some of the strangest places.

  8. #8
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    In the Sheet1 code module delete these two subs, and add the Worksheet_Change one

    See if that works better


    'Private Sub Worksheet_Activate()
    '    Call SortData(Me)
    'End Sub
    'Private Sub Worksheet_Deactivate()
    '    Call SortData(Me)
    'End Sub
    
     
    Private Sub Worksheet_Change(ByVal Target As Range)
        
        If Intersect(Target, Me.Range("B6").CurrentRegion) Is Nothing Then Exit Sub
        
        Application.EnableEvents = False
        Call SortData(Me)
        Application.EnableEvents = True
    End Sub
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  9. #9
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,033
    Location
    thanks Paul. Works great.
    Peace of mind is found in some of the strangest places.

Posting Permissions

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