VBA count and use filtered data

[bensonsearch]

Hi All,

I have filters in vba code that work fine, however is there a way to loop through them?

ie:

Do Until count = lastrow + 1
listbox1.additem (worksheets("Sheet1").range("A" & count).value
count = count + 1
loop

what i need is for it to ONLY add the filtered items not all rows (as lastrow counts all rows)

any ideas? I did a copy from sheet 1 to sheet 2 then count and load but thats just a waste and un needed etc

 

[Marcelo Branco]

Hi,

Follows a very simple example

Data in Sheet1 A1:B10, headers in row 1, like

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=128><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 width=64>Code</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>Value</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>10</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>B</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>20</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>C</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>30</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>40</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>B</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>50</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>60</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>C</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>70</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>B</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>80</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>90</TD></TR></TBODY></TABLE>

The code below filters the data using criteria Code = "A" and shows the values in column B only for filtered rows using Msgbox(es)

Sub testFilter()
    Dim rngData As Range, rngVisible As Range
    Dim aCell As Range
 
    With Sheets("Sheet1")
        'Remove the Auto Filter
        .AutoFilterMode = False
 
        'Set the data range
        Set rngData = .Range("A1:B10")
        'Apply the Auto filter to data range
        rngData.AutoFilter Field:=1, Criteria1:="A"
 
        'Set the visible range
        With rngData
            Set rngVisible = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
                  .SpecialCells(xlCellTypeVisible)
        End With
 
        'Loop through visible range
        For Each aCell In rngVisible
            MsgBox aCell.Offset(0, 1).Value
        Next aCell
    End With
 
End Sub

HTH

M.

 

[VoG]

Maybe like this

Dim c As Range
For Each c In Sheets("Sheet1").Range("A1:A" & lastrow).SpecialCells(xlCellTypeVisible)
    listbox1.AddItem c.Value
Next c

 

[bensonsearch]

Maybe like this

Dim c As Range
For Each c In Sheets("Sheet1").Range("A1:A" & lastrow).SpecialCells(xlCellTypeVisible)
    listbox1.AddItem c.Value
Next c

love it thank you. this is better than my code cause it does as I wish and is like a 5th the size.

ta

 

[bensonsearch]

Ah, now i have another question.

It copies A fine, what if i wanted A:G to appear and next to each other (ie row 1 A:G would be one add item)?

 

[VoG]

Sorry, I don't know. I've tried several ways without success. You'll probably need to copy the visible rows to another sheet then use something like

Sub a()
Dim X As Variant, lastrow As Long
lastrow = Range("A" & Rows.Count).End(xlUp).Row
X = Range("A1:G" & lastrow)
UserForm1.ListBox1.List = X
UserForm1.Show
End Sub

 

[bensonsearch]

the closest I have is

 Dim c As Range
    Dim abc As Variant
    abc = " "
    
    
For Each c In Sheets("Sheet1").Range("A2:G" & lastrow).SpecialCells(xlCellTypeVisible)
   abc = abc & c.value & " " & " "
 
  
Next c
ListBox1.AddItem abc

which works but puts all as one item, doesnt break the rows up

ie once again row 1 A:G is one item then
row 2 A:G is another

i appriciate the help, seems like a waste to then copy and paste (plus it doesnt like doing it twice in a row as it askes to paste over exsisting data)

 

[bensonsearch]

ok i have figured this one out now thanx for all the help. what I did is below (sucks but works great)

Worksheets("Sheet1").Range("A2:N" & lastrow).Copy _
Destination:=Worksheets("Sheet2").Range("A1")
    
 count = Sheets("Sheet2").Range("A1").CurrentRegion.Rows.count
 count = count + 1
 counta = 1
    
 Do Until counta = count
  asd = Sheets("Sheet2").Range("B" & counta).value & " " & " " & Sheets("Sheet2").Range("C" & counta).value & " " & " " & Sheets("Sheet2").Range("D" & counta).value & " " & " " & Sheets("Sheet2").Range("E" & counta).value & " " & " " & Sheets("Sheet2").Range("F" & counta).value & " " & " " & Sheets("Sheet2").Range("G" & counta).value
  
ListBox1.AddItem (asd)
counta = counta + 1
Loop