Get values from a a filered excel sheet 2

[gtaborda]

Hi there

I need to get each cell value for each selected row in a filtered Excel sheet.

for example, after filtering, only rows 35525 and 40125 are shown (match the filter selection)

I now need to loop (with VBA) through those 2 shown rows and get the value for range("A35525") and range("B35525") then also range("A35525") and ("B40125")

I then put these values on another sheet (I know how to do that)

Any ideas?

thanks in advance

 

[capetownandre]

The rowheight of the cell will be 0 for the unfiltered rows
therfore loop through cells check if rowheight > o



Hope this helps

 

[gtaborda]

ok thanks, the problem is that there are 40000 rows....if I loop through all 40000 testing if rowheight is 0, it simply takes too long.

This is why first I filter the records, then from those shown I look for the info I need. When I filter I get the info almost immediately, then what I need to do is loop through the rows shown/selected and extract the information...

The idea is to speed up the search process.

thanks

 

[capetownandre]

Ok

this was pretty instant for me using 40000 rows

Dim mymessage As String
Set r = Worksheets("sheet2").Range("a1:a40000")
mymessage = ""
For n = 1 To r.Rows.Count
If r.RowHeight > 0 And r(n, 1) > " " Then
mymessage = mymessage & vbCrLf & r(n, 1) & " " & n

End If
Next n
MsgBox mymessage

 

[xlbo]

no no no - no need

Use the SpecialCells(xlcelltypevisble) to define the range to loop through e.g.

For each c in Range("A2:Z40000").columns(1).specialcells(xlcelltypevisible)

  msgbox c.address
  msgbox c.offset(0,1).address

Next c

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[gtaborda]

YES !
that works great (approx 2 seconds to find the last row of data !

Thanks so much !

i thought you meant to loop like this:

for l=1 to 40000
if range("A" & l).rowheight>0 then
end if

next l

My knowledge is VBA is quite basic....sorry !

thank you so much !!!!

 

[gtaborda]

wow xlbo!

I just tried your way and also works very fast !!!!

Thanks so much to both !