hi dears,
i am using excel 2013
i try to make a traceability for our
product manufacturing and delivery
MY FORMAT IS (1 - 24 COULMNS)
A1PROD DATE A2CLIENT A3WO A4ITEM A5DIA A6QTY (M/Nos) A7ID Nos A8LENGTH (M) A9MATRL A10PR in BAR A11REMASRKS1 A12STIFFNESS A13PROD A14CLBRTN A15QC A16DLVRY A17DWG No A18TOTALMANHOURS A19TOTALWORKERS A20TIME (HRS) A21SHIFT (DAY/NIGHT) A22REMARKS3 A23DLVRY No A24DLVRY DATE
I KEEP SAME TITLES ON ROW 6 (A6 - A26)
AND DATA INPUT FROM ROW-7
MY CRITERIA - 1-5 ROW (ROW1 - TITLE)
MY RANGE - ROW 6 + (ROW6 - TITLE)
I already using a formula
its working for 1 column only at a time
i need to filter more columns at a time (wish to have filter in all d columns)
i need filtering automatically on cell entry
below is the code i am using
Private Sub Worksheet_Change(ByVal Target As Range)
'Open With structure for Target range object.
With Target
'If the cell being changes is not A2,B2,C2,D2,E2,F2,G2,H2,I2,J2,K2,L2,M2,N2,O2,P2,Q2,R2,S2,T2,U2,V2,W2,X2,Y2 or Z2 then Exit Sub.
If Intersect(Target, Range("A2:Z2")) Is Nothing Then Exit Sub
'If more than one cell is attempted to be changed then Exit Sub.
If .Cells.Count > 1 Then Exit Sub
'If the Delete key is pressed, remove AutoFilter.
If IsEmpty(Target) Then
ActiveSheet.AutoFilterMode = False
Exit Sub
End If
'Declare a String type variable for the value that was entered,
'and define the String variable.
Dim strFilter As String
strFilter = .Value
'Declare an Integer type variable for the column of the cell
'that the value was entered, and define the column.
Dim intColumn As Integer
intColumn = .Column
'Declare a Long type variable for the last used row among
'columns A, B, and C, and define that last row number.
Dim LastRow As Long
LastRow = _
Range("A:Z").Find(What:="*", After:=Range("A1"), _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
'Close the With structure.
End With
'Do some housekeeping: delete values that may still be in the
'other criteria cells. Set EnableEvents to False and back to True.
Application.EnableEvents = False
If intColumn = 1 Then
Range("B2:Z2").ClearContents
ElseIf intColumn = 2 Then
Range("A2, Z2").ClearContents
Else
Range("A2:B2").ClearContents
End If
Application.EnableEvents = True
'Before attempting to filter anything, first see if the value
'that was entered exists in the relevant column.
'If it does not, end the procedure and advise the user.
If WorksheetFunction.CountIf(Range(Cells(5, intColumn), _
Cells(LastRow, intColumn)), strFilter) = 0 Then
MsgBox "This column does not contain " & strFilter, 48, "No such animal."
ActiveSheet.AutoFilterMode = False
Exit Sub
End If
'Now, all the reasonable preparations are completed.
'Execute the filter.
ActiveSheet.AutoFilterMode = False
Range(Cells(4, intColumn), _
Cells(LastRow, intColumn)).AutoFilter _
Field:=1, Criteria1:=strFilter
'As a convenience to the user, activate cell A1.
Application.Goto Range("A1"), 1
End Sub
my
i am using excel 2013
i try to make a traceability for our
product manufacturing and delivery
MY FORMAT IS (1 - 24 COULMNS)
A1PROD DATE A2CLIENT A3WO A4ITEM A5DIA A6QTY (M/Nos) A7ID Nos A8LENGTH (M) A9MATRL A10PR in BAR A11REMASRKS1 A12STIFFNESS A13PROD A14CLBRTN A15QC A16DLVRY A17DWG No A18TOTALMANHOURS A19TOTALWORKERS A20TIME (HRS) A21SHIFT (DAY/NIGHT) A22REMARKS3 A23DLVRY No A24DLVRY DATE
I KEEP SAME TITLES ON ROW 6 (A6 - A26)
AND DATA INPUT FROM ROW-7
MY CRITERIA - 1-5 ROW (ROW1 - TITLE)
MY RANGE - ROW 6 + (ROW6 - TITLE)
I already using a formula
its working for 1 column only at a time
i need to filter more columns at a time (wish to have filter in all d columns)
i need filtering automatically on cell entry
below is the code i am using
Private Sub Worksheet_Change(ByVal Target As Range)
'Open With structure for Target range object.
With Target
'If the cell being changes is not A2,B2,C2,D2,E2,F2,G2,H2,I2,J2,K2,L2,M2,N2,O2,P2,Q2,R2,S2,T2,U2,V2,W2,X2,Y2 or Z2 then Exit Sub.
If Intersect(Target, Range("A2:Z2")) Is Nothing Then Exit Sub
'If more than one cell is attempted to be changed then Exit Sub.
If .Cells.Count > 1 Then Exit Sub
'If the Delete key is pressed, remove AutoFilter.
If IsEmpty(Target) Then
ActiveSheet.AutoFilterMode = False
Exit Sub
End If
'Declare a String type variable for the value that was entered,
'and define the String variable.
Dim strFilter As String
strFilter = .Value
'Declare an Integer type variable for the column of the cell
'that the value was entered, and define the column.
Dim intColumn As Integer
intColumn = .Column
'Declare a Long type variable for the last used row among
'columns A, B, and C, and define that last row number.
Dim LastRow As Long
LastRow = _
Range("A:Z").Find(What:="*", After:=Range("A1"), _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
'Close the With structure.
End With
'Do some housekeeping: delete values that may still be in the
'other criteria cells. Set EnableEvents to False and back to True.
Application.EnableEvents = False
If intColumn = 1 Then
Range("B2:Z2").ClearContents
ElseIf intColumn = 2 Then
Range("A2, Z2").ClearContents
Else
Range("A2:B2").ClearContents
End If
Application.EnableEvents = True
'Before attempting to filter anything, first see if the value
'that was entered exists in the relevant column.
'If it does not, end the procedure and advise the user.
If WorksheetFunction.CountIf(Range(Cells(5, intColumn), _
Cells(LastRow, intColumn)), strFilter) = 0 Then
MsgBox "This column does not contain " & strFilter, 48, "No such animal."
ActiveSheet.AutoFilterMode = False
Exit Sub
End If
'Now, all the reasonable preparations are completed.
'Execute the filter.
ActiveSheet.AutoFilterMode = False
Range(Cells(4, intColumn), _
Cells(LastRow, intColumn)).AutoFilter _
Field:=1, Criteria1:=strFilter
'As a convenience to the user, activate cell A1.
Application.Goto Range("A1"), 1
End Sub
my
