Preface
Find Highlighted cells of Filtered data is turned challenge and we thought to bring it here to help Developer. Basically, Filtering is just a hide/seek method where unwanted rows gets hidden and filtered rows gets highlighted.
Limit Vs Enhancement
Earlier in Excel 2003 we were not having code or methods to read filtered data hence, we used to put check for filtered range where row is not hidden. But in Microsoft Excel 2007 above we have advanced level of filters and their objects by which we can manipulate data using xlVisibleCells.
Solution
After applying Filter, Excel breaks the filtered range into Areas. Most of the developer does not know it and directly try over iterating xlVisibleCells cause wrong data.
C# code example
public void ReadFilteredData()
{
//this is the range which have filtered records
Range filterRange = sheet.UsedRange.SpecialCells(XlCellType.xlCellTypeVisible);
//Iterate each area in filtered range
for (int i = 1; i <= filterRange.Areas.Count; i++)
{
//Read first area into a range
Range areaRange = filterRange.Areas.get_Item(i);
//populate entire range into a two dimensional array
object[,] areaValues = areaRange.Value2;
//Get lenth of the object
//Put a loop to iterate over the values
for(int i=0; i< lenth; i++)
{
//Do whatever you want here
}
}
}
VB.Net code example
Private Sub ReadFilterText_Click(sender As Object, e As RibbonControlEventArgs) Handles ReadFilterText.Click)
Dim oWorkSheet As excel.Worksheet
'Set worksheet reference
oWorkSheet = Globals.ThisAddIn.Application.ActiveSheet
'Get range
Dim oRange As excel.Range
oRange = oWorkSheet.UsedRange.SpecialCells(excel.XlCellType.xlCellTypeVisible)
'Iterate area
Dim areaCount As Integer
For areaCount = 1 To oRange.Areas.Count
'Read first area into range
Dim aRange As excel.Range
aRange = oRange.Areas.Item(areaCount)
oRange.Select
Next areaCount
End Sub
VBA code example
Public Sub GetFilterdData()
Dim oWorkSheet As Worksheet
'Set worksheet reference
Set oWorkSheet = ActiveSheet
'Get range
Dim oRange As Range
Set oRange = oWorkSheet.UsedRange.SpecialCells(xlCellTypeVisible)
'Iterate area
Dim areaCount As Integer
For areaCount = 1 To oRange.Areas.Count
'Read first area into range
Dim aRange As Range
Set aRange = oRange.Areas.Item(areaCount)
oRange.Select
Next areaCount
End Sub
Before code execution
Post code execution
