AutoFilter

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello all, I'm having a problem with the following. I'm trying to only select
what is filter and nothing elso, but I have not been able to find the right
code(s) to do so. Thanks

Sub yellowtotal()
Selection.AutoFilter Field:=4, Criteria1:="=*total*", Operator:=xlAnd
Range("A2:W98").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Interior.ColorIndex = 36
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Font.Bold = True
ActiveSheet.ShowAllData
Range("A2").Select
End Sub
 
When you record your macro, add one more step.

Apply the filter
filter contains Total
select the range
edit|goto|special|visible cells only

And continue with your recording.
 
Ok, but the problem would be that the data changes daily. So, I can not
select the rang. I need to just select the filter data only.
 
How about:

Option Explicit
Sub testme()
Dim VisRng As Range
Dim RngToFilter As Range

With ActiveSheet
'turn off any existing autofilters
.AutoFilterMode = False

'change the columns to what you need
Set RngToFilter = .Range("a:x")

'clear any formatting
RngToFilter.Interior.ColorIndex = xlNone

RngToFilter.AutoFilter Field:=4, Criteria1:="=*total*"
With .AutoFilter.Range
If .Columns(1).Cells.SpecialCells(xlCellTypeVisible).Count = 1 Then
MsgBox "only the headers are visible"
Exit Sub
End If
'resize to avoid the header
'and come down one row
Set VisRng = .Resize(.Rows.Count - 1).Offset(1, 0) _
.Cells.SpecialCells(xlCellTypeVisible)
End With

With VisRng
.Interior.ColorIndex = 36
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
.Borders(xlInsideVertical).LineStyle = xlNone
.Font.Bold = True
End With

.ShowAllData
End With

End Sub
 
Awesome!!

Now I just have one more thing, and I will be done with spread sheet. Do you
anything about "sum"?
 
It adds the numbers in a range.

If you're working with data|filter|autofilter, you may want to look at excel's
help for =subtotal(). It will ignore cells on rows that are hidden by
autofilter.
Awesome!!

Now I just have one more thing, and I will be done with spread sheet. Do you
anything about "sum"?
Click to expand...
 
Dave,
Could you help me to modify this code to be visible the selected filters
instead of changed its color?
Thanks,
JT
 
I don't understand what you mean.


Dave,
Could you help me to modify this code to be visible the selected filters
instead of changed its color?
Thanks,
JT
Click to expand...
 
Back
Top