PDA

View Full Version : [SOLVED] Macro filtered in column no fill cells in selected sheets



k0st4din
11-08-2015, 11:25 PM
Hello everybody,
about a week ago on this site (http://www.mrexcel.com/forum/excel-questions/899632-macro-filtered-no-fill-cells.html) I asked for help to make a macro, but to this day I have no answer, and I am now writing to you about a cooperation.This is what I'm trying to do:
In column A2 (here I have a filter), down from a3 have text lines are painted in several colors (orange, green, red) and there are not colored (no fill).
This column is the same in all other 46 sheets (sales, city 1, product 2, product 1, etc.).
I want to make a macro and a button with which it pushed, only filters no fill cells in my sheets. When pressed a second time to button my column back in its original form.
Thank you once again.

Sub fillnofill()
Sheets("Blag total").Select 'how to add the names of the other sheets?
Range("A2").Select
ActiveSheet.Range("A2").AutoFilter Field:=1, Operator:= _
xlFilterNoFill
'how to add something that I one button to perform two actions?
Range("A2").Select
ActiveSheet.Range("A2").AutoFilter Field:=1
Range("A2").Select
End Sub

NoSparks
11-10-2015, 10:18 AM
This doesn't answer your question, although it does filter according to interior cell color, sort of....
it filters for yellow, it filters for green, it won't filter for white. I don't know why, perhaps someone else knows.


With ActiveSheet
If .AutoFilterMode = False Then
'RGB(255, 255, 0) is yellow
'RGB(146, 208, 80) is green
'RGB(255, 255, 255) is white
.Range("A2").AutoFilter Field:=1, Criteria1:=RGB(146, 208, 80), Operator:=xlFilterCellColor
Else
.AutoFilterMode = False
End If
End With


From your file, link to on the other forum, here's what I used to get the RGB colors


Sub GetRGBofCell()
Dim R As Integer
Dim G As Integer
Dim B As Integer
Dim RGB As Long
RGB = ActiveCell.Interior.Color
R = RGB And 255
G = RGB \ 256 And 255
B = RGB \ 256 ^ 2 And 255
MsgBox R & "," & G & "," & B
End Sub

k0st4din
11-10-2015, 01:14 PM
Hello NoSparks,
My macro is made by hand and do what I want. I just do not know how to add specific names of sheets and how one button to filter and return the entire column of the table.

..................Operator:= xlFilterNoFill ' this filtered no fill cells
Thank you very much.

NoSparks
11-10-2015, 04:08 PM
Maybe something like this ?


Sub test_fillnofill()
Dim i As Integer
Dim shtArray As Variant

shtArray = Array("Sheet1", "Sheet3", "Sheet6")

For i = LBound(shtArray) To UBound(shtArray)
With Worksheets(shtArray(i))
If .AutoFilterMode = False Then
.Range("A2").AutoFilter Field:=1, Operator:=xlFilterNoFill
Else
.AutoFilterMode = False
End If
End With
Next i

End Sub

k0st4din
11-10-2015, 10:09 PM
So, so that's what I tried to accomplish.
Please only when I return all my rows to remain filters (now by clicking the button a second time shows all rows but disappear filters).
If left so I would have to put filters over 46 sheets - which is not much fun. :)
Thanks infinite.

NoSparks
11-11-2015, 07:59 AM
Sorry, I don't understand.
Maybe this guess is closer.



Sub test2_fillnofill()
Dim i As Integer
Dim shtArray As Variant

shtArray = Array("Sheet1", "Sheet3", "Sheet6")

For i = LBound(shtArray) To UBound(shtArray)
With Worksheets(shtArray(i))
If .AutoFilterMode = False Then
.Range("A2").AutoFilter Field:=1, Operator:=xlFilterNoFill
ElseIf (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Then
On Error Resume Next
.ShowAllData
On Error GoTo 0
Else
.Range("A2").AutoFilter Field:=1, Operator:=xlFilterNoFill
End If
End With
Next i

End Sub

k0st4din
11-11-2015, 10:54 AM
Hello NoSparks,
everything is alright now.
Correct me understand. Now I have a filter - when all rows are displayed.
Mark thread as resolved.
Many thanks
Be alive and well