Selecting the first, second, third row of filtered data.

[RockandGrohl]

Hello all,

Have this excellent piece of code which selects the data on the first visible row, column B

ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 2).Value

I want to run a for loop as I would like to select some cells in the first visible row, then the second, then the third etc.

So in my mind, it would be like this:

x = 1
For x = 1 To 10
newtref = ActiveSheet.AutoFilter.Range.Offset(x).SpecialCells(xlCellTypeVisible).Cells(x, 1).Value

' switch to another sheet and dump the data off here
' switch back to the filtered sheet
Next x

This would give me the first row, column A of data which is the new "tref"

And I was hoping by doing range.offset(x) which would then be 2, then 3, then 4 it would work.

Unfortunately not however as it seems to be able to get the first filtered row of data, but none after it.

Anyone have a solution on where I can just select the second, third, fourth etc row of data? Cheers!

 

[jasonb75]

Something like this should work, I've not tried your method of identifying the filtered range so that syntax may need correcting.

Dim rng As Range, c As Range
Set rng = ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible)
For Each c In rng.Columns(1)
    newtref = c.Value
' switch to another sheet and dump the data off here
' switch back to the filtered sheet
Next c

 

[RockandGrohl]

Something like this should work, I've not tried your method of identifying the filtered range so that syntax may need correcting.

Dim rng As Range, c As Range
Set rng = ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible)
For Each c In rng.Columns(1)
    newtref = c.Value
' switch to another sheet and dump the data off here
' switch back to the filtered sheet
Next c

Hi Jason,

Thanks for your reply I want to basically do two things here:

One is select multiple ranges of data on a given row. So that would be column 1, column 3, column 4, column 6, column 7 and column 13. Each would be loaded as a variable and then passed to another sheet.

The next is for now I want to select 10 individual rows, but another time I may want to select 20, or just 5, so I'd like to keep that loop within "x"

Cheers.

 

[jasonb75]

One is select multiple ranges of data on a given row.

Your original code only allowed for one column.

The next is for now I want to select 10 individual rows, but another time I may want to select 20

That wasn't strictly specified in your first post, but now that you mention it, I can see the logic of the loop being 1 to 10.

Is there anything else that could be easily overlooked before I re-write the code?

 

[RockandGrohl]

Your original code only allowed for one column.

That wasn't strictly specified in your first post, but now that you mention it, I can see the logic of the loop being 1 to 10.

Is there anything else that could be easily overlooked before I re-write the code?

Yep, but I could have more lines to get more columns, as each line of code was for one specific reference

newtref = ActiveSheet.AutoFilter.Range.Offset(x).SpecialCells(xlCellTypeVisible).Cells(x, 1).Value
newprice = ActiveSheet.AutoFilter.Range.Offset(x).SpecialCells(xlCellTypeVisible).Cells(x, 5).Value
newduration = ActiveSheet.AutoFilter.Range.Offset(x).SpecialCells(xlCellTypeVisible).Cells(x, 4).Value

etc

No that should be it thanks.

 

[jasonb75]

Without knowing where the destination of each of the 6 columns, I would say something like this, however I suspect that this could be done without looping at all, you certainly don't need to 'switch' to another sheet, the values of each cell could be sent to the other sheet without assigning them to variables first.

Dim rng As Range, c As Range, x As Long
Set rng = ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible)
For Each c In rng.Columns(1)
    If x = 10 Then Exit For
    newtref = c.Value
    newprice = c.Offset(, 4).Value
    newduration = c.Offset(, 3).Value
' switch to another sheet and dump the data off here
' switch back to the filtered sheet
    x = x +1
Next c