Filter excel and color rang

Help Activities
1

Hi My Requirement is
filter an excel sheet and colour the filtered results alone. thats all . i dont want to extract the Filtered results as data table etc.,
i went through forums and filter table activity inside excel activities also. what they mean by table in filter table activity ?
all i have is an excel sheet. which generated dynamically , where will i search for a table there ?

2

I can use set range color or via a VB script but all those are slow. say for 5000 rows it takes more than 30-40 minutes.
the point here is the 5000 does not fall under one range. A5:V25 → 20 rows then A73-V115-42 rows likethat. some times a range is a simple one or two rows. so above methods are in efficient

3

i want to apply a method which is as simple and fast as clicking image and fill colour thats all!

4

@Seetharaman_K – What is your Filter condition? Your requirement is still not clear at all…

5

my filter condition is based on couple of values of 2 columns. say if column1 = “A” or “B” then i wantto colour those rows.

6

@Seetharaman_K - Please chk here
Color Excel

7

Hi,

You could use VB.NET here within an invoke code activity, it should be much more faster. See attached file.

FilterRows.txt (1.3 KB)

1 Like
8

i did use a script. it just took half the time of the colour range activity. not much of an improvement i will say. but found a way.
create a table using create table activity (basically make the entire excel as a table)
filter the table using filter table activity
then colour entire excel , (now it will colour only the filtered result)
now am looking a way to take out the filter or delete the filter. so that when user opens the file back they wont see the filter.

9

In the VB script, did you specify

.SpecialCells(xlCellTypeVisible)

when filtering?

This will activate only the visible cells. If you do not do this you will still have the hidden cells, slowing down the running time.

If it takes 30-40 minutes to set a color of only 5000 rows, there is probably some bug in the VBnet code. If implemented correctly, it should be quick.

10

I had a similar problem. I filtered a big table, and looped through the AutoFilter results like this:

For Each r In .Range(…).Rows.SpecialCells(xlCellTypeVisible)

Here, .Range(…) is just symbolizing the range I was left with after filtering.

When I set SpecialCells(xlCellTypeVisible) I experienced a significant improvement in running time.

11

i did the auto filter results do u know how do i color the result. there is a range also in my case . ie , i need not colour all columns i will have to omit last 8 columns . any idea how to get that ? i tried this xlCellTypeVisible but my code is saying its not declared and according to documentation xlCellTypeVisible is type under excel application. but when i try to declare its not working.

xlApp = New Microsoft.Office.Interop.Excel.Application
xlWorkBook = xlApp.Workbooks.Open(FilePath)
xlApp.DisplayAlerts = False
xlWorkBook.Activate()
xlWorkSheet = CType(xlWorkBook.Worksheets.Item(“Sheet1”), Microsoft.Office.Interop.Excel.Worksheet)
xlworksheet.UsedRange.AutoFilter(1, “818714”)
Dim TFiltro As Range
TFiltro = xlWorkSheet.UsedRange.SpecialCells(xlApp.)
TFiltro.Interior.ColorIndex = 10
xlWorkBook.Save()
xlWorkBook.Close(False)
xlApp.Quit()
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp)
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkBook)
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkSheet)
GC.Collect()

12

can u give me some example for this ?

13

instead of giving “A1 , A2” to be compared i want the column name as input .

14

I am not exactly sure what you mean but here is an example.

It is not the shortest or most crisp code, but I think it is good for understanding.

With Tfiltro
'Rule: if cell B is 12345678 and cell D and E are empty, insert 5 in D and 123 in E	
    'Filter
    .AutoFilter Field:=2, Criteria1:="=12345678"
    .AutoFilter Field:=4, Criteria1:="="
    .AutoFilter Field:=5, Criteria1:="="
	With Sheets(1)
        'If there are any rows like this
        If .Range("A5", Range("A5").End(xlDown).End(xlDown).End(xlUp)).Count > 1 Then
            'Loop through the rows in the filtered range. Note: since the rows in the selection are not (always)
            'adjacent, you can not copy the whole column at once. Then, you'd get a "multiple selection" error.
            '
            'For each row in the range from cell A6 on, do something to the cells which are currently visible.
            For Each r In .Range("A6", Range("A6").End(xlDown).End(xlDown).End(xlUp)).Rows.SpecialCells(xlCellTypeVisible)
                'Fill D with 5
                Cells(r.Row, 4).Value = "5"
                'Fill E with 123
                Cells(r.Row, 5).Value = "123"
                'Color them!
                Cells(r.Row, 4).Interior.ColorIndex = 42  ' Ocean
                Cells(r.Row, 5).Interior.ColorIndex = 42  ' Ocean
            Next r
        End If
    End With
    'Reset all filters
    .AutoFilter Field:=5
    .AutoFilter Field:=4
    .AutoFilter Field:=2
End With

Note: for the excel sheet for which this Makro was made, the first 5 rows were just header information. Therefore the filtering is set on row 5. And the loop goes from row 5 on.