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

RockandGrohl

Well-known Member
Joined
Aug 1, 2018
Messages
594
Office Version
  1. 2010
Platform
  1. Windows
Hello all,

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

VBA Code:
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:


VBA Code:
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!
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,637
Office Version
  1. 365
Platform
  1. Windows
Something like this should work, I've not tried your method of identifying the filtered range so that syntax may need correcting.
VBA Code:
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

Well-known Member
Joined
Aug 1, 2018
Messages
594
Office Version
  1. 2010
Platform
  1. Windows
Something like this should work, I've not tried your method of identifying the filtered range so that syntax may need correcting.
VBA Code:
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

Well-known Member
Joined
Dec 30, 2008
Messages
12,637
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Aug 1, 2018
Messages
594
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
12,637
Office Version
  1. 365
Platform
  1. Windows
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.
VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,685
Messages
5,637,809
Members
416,983
Latest member
LessThanAverageUser

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top