Imports System
Imports System.IO
Imports Microsoft.VisualBasic.FileIO
Imports Microsoft.Office.Interop
Module Program
Dim oxl As Excel.Application
Dim owbs As Excel.Workbooks
Dim owb As Excel.Workbook
Dim osheets As Excel.Worksheets
Dim osheet As Excel.Worksheet
Dim owr, newRng, rngVisible As Excel.Range
Sub Main()
oxl = CreateObject("Excel.Application")
oxl.Visible = True
owb = CType(oxl.Workbooks.Open("G:\TestData\Matthews Fund"), Excel.Workbook)
osheet = CType(owb.Worksheets("Holdings"), Excel.Worksheet)
owr = CType(osheet.Range("A13:G68"), Excel.Range)
'Resizing selected range to exclude header row
newRng = owr.Offset(1).Resize(owr.Rows.Count - 1)
'Applying autofilter on the resized range and setting criterion to field 3
newRng.AutoFilter(Field:=3, Criteria1:="=India")
'Trying to select the filtered rows
'but this step is not working properly as non adjacent rows are not getting selected
rngVisible = newRng.SpecialCells(Excel.XlCellType.xlCellTypeVisible)
'Row 14 and Row 26 are 2 rows which are getting filtered out based on the condition
'However no of rows is displayed as 1 and not 2 - only row 14 is being selected
'Probable reason: Row 14 and Row 26 are not adjacent
Console.WriteLine("No of filtered rows: {0}", rngVisible.Rows.count)
'Any easy/direct way to select the non-adjacent filtered rows in a single range obj?
Console.ReadLine()
End Sub
End Module
As explained in the comments in the code, the result of applying the auto filter to newRng is that two rows, Row 14 and Row 26 are getting filtered out. But when I try to select those two rows, I am unable to. So, is there any easy/direct way to select the non-adjacent filtered rows in a single range obj?
A final point: I found some useful info here. But the solution here seems to be deleting the filtered rows. i do not want to delete the filtered rows.
