How do I capture the range of cells displayed after performing an AutoFilter?

In my spreadsheet I filter for a specific value in a specific column. After the filter runs the rows that are returned are not continuous. I need to look at each returned row and perform some work on cells in that row.

I thought about walking through the list but I do know how to determine where to star and what do to when the numbers are not continuous. This seems easy enough because I can do this manually. I need a kick start to understand how to do this in code.

If this makes sense any help would be appreciated.

--
Rick A

Have a look in vba help index for SPECIALCELLS (xlvisible)

--
Don Guillett
SalesAid Software

"Rick A" wrote in message ...
How do I capture the range of cells displayed after performing an AutoFilter?

In my spreadsheet I filter for a specific value in a specific column. After the filter runs the rows that are returned are not continuous. I need to look at each returned row and perform some work on cells in that row.

I thought about walking through the list but I do know how to determine where to star and what do to when the numbers are not continuous. This seems easy enough because I can do this manually. I need a kick start to understand how to do this in code.

If this makes sense any help would be appreciated.

--
Rick A

If I understand correctly...

- You have a sheet containing a number of rows
- When the Autofilter is applied this number is obviously reduced and
you are left with a selection particular selection of existing rows
- You want to grab this filtered selection as a range so you can work
with it?

If this is the case then I think this should work.

<after autofilter has been applied

Set MyRange = Sheet1.UsedRange.SpecialCells(xlCellTypeVisible)


I think that should work. By using the UsedRange property it will
return a range starting with the upper left most point in the
spreadsheet with data in it, right down the lower right most point with
data in it.

If you only wish to return a certain selection of columns though,
you'll need to adjust it to read:-

Sheet1.Range("B1:L" &
Sheet1.UsedRange.Rows.Count).SpecialCells(xlCellTy peVisible)

Obviously replace the "B1:L" to whatever is needed.

I hope I've got the right idea.

Rob

Rob,

You understand correctly.

I'm going to give what you suggest a try.

Thanks,

--
Rick
wrote in message
oups.com...
If I understand correctly...

- You have a sheet containing a number of rows
- When the Autofilter is applied this number is obviously reduced and
you are left with a selection particular selection of existing rows
- You want to grab this filtered selection as a range so you can work
with it?

If this is the case then I think this should work.

<after autofilter has been applied

Set MyRange = Sheet1.UsedRange.SpecialCells(xlCellTypeVisible)


I think that should work. By using the UsedRange property it will
return a range starting with the upper left most point in the
spreadsheet with data in it, right down the lower right most point with
data in it.

If you only wish to return a certain selection of columns though,
you'll need to adjust it to read:-

Sheet1.Range("B1:L" &
Sheet1.UsedRange.Rows.Count).SpecialCells(xlCellTy peVisible)

Obviously replace the "B1:L" to whatever is needed.

I hope I've got the right idea.

Rob

Rob,

Here's my code.

Private Sub ProcessIndividuals()
Dim strPath As String
Dim PriorityExcel As Excel.Application
Dim PrioritySheet As Excel.Worksheet
Dim IndividualRange As Range
Dim IndividualCell As Range
Dim PriorityIndividualRange As Range

Dim strIndividual As String

strPath = ThisWorkbook.ActiveSheet.Range("DirectoryName") &
ThisWorkbook.ActiveSheet.Range("FileName")

Set PriorityExcel = New Excel.Application
With PriorityExcel 'open the "priority" file
.Workbooks.Open Filename:=strPath
Set myWorkbook = .ActiveWorkbook
End With

PriorityExcel.Visible = True ' turn this only only to see what is going on

' Process each name in the Individual range on the master spreadsheet
Set IndividualRange = ThisWorkbook.ActiveSheet.Range("Individuals")
For Each IndividualCell In IndividualRange
Select Case IndividualCell.Value
Case "start", "end" ' do not process the start and end keywords
Case Else
If blnDebug Then Debug.Print IndividualCell.Value ' print the value of
the cell to see what it is
strIndividual = IndividualCell.Value ' save the cell value in a
variable to make code easier to read
' process all sheets in the "slave" workbook
For Each PrioritySheet In PriorityExcel.ActiveWorkbook.Worksheets
Select Case PrioritySheet.Name
Case "Calendar", "Awaiting approval" ' do not look for individuals
on these two worksheets
Case Else
' let's go looking for the individual on the active sheet
PriorityExcel.Worksheets(PrioritySheet.Name).Activ ate
PrioritySheet.Range("B:B").AutoFilter _
field:=1, _
Criteria1:=strIndividual, _
VisibleDropDown:=False
Set PriorityIndividualRange =
PrioritySheet.UsedRange.SpecialCells(xlCellTypeVis ible)
Debug.Print PriorityIndividualRange.Columns.Count
Debug.Print PriorityIndividualRange.Rows.Count '<----- not
showing what I expect - is showing just 1
Selection.AutoFilter
If blnDebug Then Debug.Print PrioritySheet.Name
End Select
Next PrioritySheet
End Select
Next IndividualCell
'Save and close the workbook and sheet
myWorkbook.Close savechanges:=xlSaveChanges
PriorityExcel.Quit
Set PrioritySheet = Nothing
Set myWorkbook = Nothing
Set PriorityExcel = Nothing
End Sub


This line should produce a row count. What I see is a count of one. I
should see more than one and a variable amount based on the individual
selected.
Debug.Print PriorityIndividualRange.Rows.Count

Can you help?

--
Rick Allison
wrote in message
oups.com...
If I understand correctly...

- You have a sheet containing a number of rows
- When the Autofilter is applied this number is obviously reduced and
you are left with a selection particular selection of existing rows
- You want to grab this filtered selection as a range so you can work
with it?

If this is the case then I think this should work.

<after autofilter has been applied

Set MyRange = Sheet1.UsedRange.SpecialCells(xlCellTypeVisible)


I think that should work. By using the UsedRange property it will
return a range starting with the upper left most point in the
spreadsheet with data in it, right down the lower right most point with
data in it.

If you only wish to return a certain selection of columns though,
you'll need to adjust it to read:-

Sheet1.Range("B1:L" &
Sheet1.UsedRange.Rows.Count).SpecialCells(xlCellTy peVisible)

Obviously replace the "B1:L" to whatever is needed.

I hope I've got the right idea.

Rob

Hi,

That's interesting.

Step through your code and after executing this line:-

PrioritySheet.Range("B:B").AutoFilter _
field:=1, _
Criteria1:=strIndividual, _
VisibleDropDown:=False

Take a look at the sheet and see how many rows are visible.

Rob

Rob,

I did what you suggested and I can see 27 rows the first time.

What's interesting is this.

Row one (1) displays, that's where the filter is located. I have
VisibleDropDown:=False so I do not see the drop down arrow but I know it is
there. The next row that displays is row four (4). After that in this case
the rows are all sequential. As I watch the code run, different rows
display based on the filter that are not always continuous. I have 15
different filters I am using.

I've written this code so it runs from a master excel file and references
another excel file. Is there something that I could be missing because I'm
running code in one file that is referencing another file?

Thank you for your help.

--
Rick Allison
wrote in message
ups.com...
Hi,

That's interesting.

Step through your code and after executing this line:-

PrioritySheet.Range("B:B").AutoFilter _
field:=1, _
Criteria1:=strIndividual, _
VisibleDropDown:=False

Take a look at the sheet and see how many rows are visible.

Rob