filtered data showing on listbox - userform

[gkisacik]

Hi,

i am trying to figure out why the following code doesnt show only filtered data on the listbox. Can someone help me please?


Dim rData As Range
With Blad1
Set rData = .Range(.Cells(1, 1), .Cells(.Rows.Count, 5).End(xlUp))
If Not .AutoFilterMode Then .Cells(1, 1).AutoFilter
.Cells(1, 1).AutoFilter field:=5, Criteria1:=txtuser.Text
On Error Resume Next
Set rSource = .AutoFilter.Range.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
.Cells(1, 200).CurrentRegion.ClearContents
rSource.Copy .Cells(1, 200)
Set rSource = .Cells(1, 200).CurrentRegion
Set rSource = rSource.Offset(1, 0).Resize(rSource.Rows.Count - 1, rSource.Columns.Count)
With frmmain.ListBox1
.RowSource = ""
.RowSource = rSource.Address(external:=True)
End With
End With
Unload Me
End If


Thanks,

Gokhan Kisacik

 

[Fluff]

There's nothing obviously wrong with the code, other than an End If which shouldn't be there.
Does the data get filtered correctly?

 

[gkisacik]

the data on the worksheet filtered correctly, unfortunately listbox doesnt populate the filtered data, filtered data comes first on the listbox then from last filtered row, it shows the rest of the data on the listbox while worksheet filtered perfectly.

End if was part the code above that section, sorry i should not include it here

 

[Fluff]

Does the data in cells(1,200) just show the data you want in the list box?
Also are you getting to much, or to little data in the listbox?

 

[gkisacik]

The complete data is not really (1,200). Perhaps i should put loop until last row (filtering, not sure how i should adapt this)

After filtering listbox shows extra rows which were filtered. For instance the data has 30 rows, after filtering , filtered data comes on the top of list of listbox, it adds also the rest of rows after the last filtered row.

 

[Norie]

Have you checked what range(s) rSource consists of?

If it's based on the results of a filter then it's likely to consist of multiple non-contiguous ranges and that's not something you can use for the RowSource of a listbox.

 

[gkisacik]

Hi,

how should i fix this? Perhaps i am doing something wrong although i try to copy/select the cells visible to show them on the listbox.

        Dim rData  As Range
With Blad1
Set rData = .Range(.Cells(1, 1), .Cells(.Rows.Count, 5).End(xlUp))
If Not .AutoFilterMode Then .Cells(1, 1).AutoFilter
.Cells(1, 1).AutoFilter field:=5, Criteria1:=txtuser.Text
On Error Resume Next
Set rSource = .AutoFilter.Range.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
.Cells(1, 200).CurrentRegion.ClearContents
rSource.Copy .Cells(1, 200)
Set rSource = .Cells(1, 200).CurrentRegion
Set rSource = rSource.Offset(1, 0).Resize(rSource.Rows.Count - 1, rSource.Columns.Count)
With frmmain.ListBox1
        .RowSource = ""
        .RowSource = rSource.Address(external:=True)
End With
End With
Unload Me

 

[Norie]

You should put the filter data into an array and then use that to populate the listbox.

 

[Fluff]

How about

   Dim rData As Range
   With Blad1
      .UsedRange.AutoFilter field:=15, Criteria1:=Me.txtuser.Text
      On Error Resume Next
      Set rSource = .UsedRange.SpecialCells(xlCellTypeVisible)
      On Error GoTo 0
      .Cells(1, 200).CurrentRegion.ClearContents
      rSource.Copy .Cells(1, 200)
      Set rSource = .Cells(1, 200).CurrentRegion
      Set rSource = rSource.Offset(1, 0).Resize(rSource.Rows.Count - 1, rSource.Columns.Count)
      With Me.ListBox1
         .RowSource = ""
         .RowSource = rSource.Address(external:=True)
      End With
   End With
   Unload Me

 

[gkisacik]

Hi Fluff,
unfortunately i get an error 1004 at rSource.Copy.Cells(1,200) about overlapping problem

Filtering works in at the background