AutoFilter

[pgarcia]

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

 

[Dave Peterson]

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.

 

[pgarcia]

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.

 

[Dave Peterson]

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

 

[pgarcia]

Awesome!!

Now I just have one more thing, and I will be done with spread sheet. Do you
anything about "sum"?

 

[Dave Peterson]

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"?

 

[JT]

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

 

[Dave Peterson]

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