Re-set autofilter selection after adding a new row - programmatica

[Guest]

I have a worksheet that has an autofilter on a column called Assigned DFE.
When a user uses this filter they will just see the rows that pertain to
them. I created a command button that allows them copy one of their existing
rows to the next available row, but when this happens they must re-select
their name in the Assigned DFE column in order to see the new row that was
added. How can I get my command button to automatically show the new row
that was added without them having to do that extra step?

Thank you,

Sarah

 

[Guest]

After the commandbutton code copies the row, it should remove the autofilter
or showAll and reapply it with the users name as the criteria.

 

[Guest]

That sounds like it'ss work. Do you know the command for finding out what
was selected for a filter?

 

[Guest]

If the user has selected a cell on a visible row in the autofilter and
presses the command button, it will copy that row to the bottom of the filter
range and reapply the filter to include that row and the existing criteria.

Private Sub Commandbutton1_Click()
Dim flt As Filter
Dim sCrit1() As String, sCrit2() As String
Dim sOp() As Long, rng As Range, rng1 As Range
Dim rng2 As Range, rng3 As Range
Dim i As Long, cnt As Long
Dim rng4 As Range
If ActiveSheet.AutoFilterMode Then
Set rng = ActiveSheet.AutoFilter.Range
Set rng4 = Intersect(rng, ActiveCell)
If ActiveCell.Row = rng.Row Or _
rng4 Is Nothing Or _
ActiveCell.EntireRow.Hidden Then
MsgBox "Select a visible row to reproduce" & _
"in the filtered data"
Exit Sub
End If
i = 0
cnt = ActiveSheet.AutoFilter.Filters.Count
ReDim sCrit1(1 To cnt)
ReDim sCrit2(1 To cnt)
ReDim sOp(1 To cnt)
For Each flt In ActiveSheet.AutoFilter.Filters
i = i + 1
If flt.On Then
sCrit1(i) = flt.Criteria1
sCrit2(i) = ""
sOp(i) = 2
On Error Resume Next
sCrit2(i) = flt.Criteria2
sOp(i) = flt.Operator
On Error GoTo 0
End If
Next
ActiveSheet.ShowAllData
Set rng1 = rng.Columns(1).Cells
Set rng2 = rng(rng.Count + 1)
Set rng3 = Intersect(rng, ActiveCell.EntireRow)
rng3.Copy rng2
Set rng = rng.Resize(rng.Rows.Count + 1)
ActiveSheet.AutoFilterMode = False
rng.AutoFilter
For i = 1 To cnt
If sCrit1(i) <> "" Then
If sCrit2(i) <> "" Then
rng.AutoFilter Field:=i, Criteria1:=sCrit1(i), _
Operator:=sOp(i), _
Criteria2:=sCrit2(i)
Else
rng.AutoFilter Field:=i, Criteria1:=sCrit1(i)
End If
End If
Next
End If
End Sub

 

[Guest]

That was a bit more than I was looking for. I'm not that good with coding.
So there isn't a command that tells me what was selected for an autofilter?

For example I have a column called DFE. i've applied the autfilter to it and
now when the user makes a selection, I would like to know where this
selection is stored so that I can access it in the code.

 

[Tom Ogilvy]

I gave you the information.

It is stored in three methods of the filter object

Criteria1
Criteria2
Operator

If the user will only select an item from the dropdown, then you only need
to look at Criteria1.

 

[Guest]

Thanks Tom. I wasn't seeing it before. That worked great!