RE: Re-set autofilter selection after adding a new row - programma

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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" wrote:

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

--
Regards,
Tom Ogilvy


"Sarah" wrote:

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

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy


"Sarah" wrote:

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


.

Relevant Pages

  • RE: Re-set autofilter selection after adding a new row - programma
    ... range and reapply the filter to include that row and the existing criteria. ... Dim sCrit1() As String, sCrit2As String ... Dim i As Long, cnt As Long ... I created a command button that allows them copy one of their existing ...
    (microsoft.public.excel.programming)
  • Re: query after insert fails
    ... event in the form open code for the form that uses the query. ... Based on the selection criteria the search then inserts ... Dim IntPos1 As Integer ... Dim oCmd As Command ...
    (microsoft.public.access.modulesdaovba)
  • Re: query after insert fails
    ... event in the form open code for the form that uses the query. ... Based on the selection criteria the search then inserts selected ... Dim IntPos1 As Integer ... Dim oCmd As Command ...
    (microsoft.public.access.modulesdaovba)
  • Re: VBA to manage user input in a workbook
    ... The ThisWorkbook module just has a reference to the current workbook, ... Dim wb As Excel.Workbook ... Dim i As Integer, j As Integer, cnt As Integer, tmp As String ... each selection. ...
    (microsoft.public.excel.programming)
  • Re: VBA to manage user input in a workbook
    ... I'm never sure of these thing, is the code placed behind the worksheet, ... Dim wb As Excel.Workbook ... Dim i As Integer, j As Integer, cnt As Integer, tmp As String ... each selection. ...
    (microsoft.public.excel.programming)