Filtered or Highlighted Cells Excel with code example

Filtered or Highlighted Cells Excel with code example

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

Next >> Understand Area in Excel with code example

Leave a Reply

Your email address will not be published.