View Full Version : auto filter on adding rows
austenr
11-17-2017, 04:24 PM
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.
Paul_Hossler
11-17-2017, 05:50 PM
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, ...?
austenr
11-18-2017, 10:58 AM
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.
Paul_Hossler
11-18-2017, 11:43 AM
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
austenr
11-20-2017, 09:07 AM
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?
Paul_Hossler
11-20-2017, 11:13 AM
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
20991
3. The event can be changed to Worksheet_Change, but that seemed like too many firings
austenr
11-20-2017, 11:38 AM
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???
Paul_Hossler
11-20-2017, 12:24 PM
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
austenr
11-20-2017, 01:05 PM
thanks Paul. Works great.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.