| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFilter
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
AutoFilter
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 wrote: 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
AutoFilter
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" wrote: 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 wrote: 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
AutoFilter
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 wrote: 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" wrote: 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 wrote: 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 -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
AutoFilter
Awesome!!
Now I just have one more thing, and I will be done with spread sheet. Do you anything about "sum"? "Dave Peterson" wrote: 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 wrote: 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" wrote: 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 wrote: 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 -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
AutoFilter
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. pgarcia wrote: Awesome!! Now I just have one more thing, and I will be done with spread sheet. Do you anything about "sum"? "Dave Peterson" wrote: 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 wrote: 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" wrote: 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 wrote: 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 -- Dave Peterson -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
AutoFilter
Dave,
Could you help me to modify this code to be visible the selected filters instead of changed its color? Thanks, JT "Dave Peterson" wrote: 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. pgarcia wrote: Awesome!! Now I just have one more thing, and I will be done with spread sheet. Do you anything about "sum"? "Dave Peterson" wrote: 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 wrote: 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" wrote: 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 wrote: 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 -- Dave Peterson -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
AutoFilter
I don't understand what you mean.
JT wrote: Dave, Could you help me to modify this code to be visible the selected filters instead of changed its color? Thanks, JT "Dave Peterson" wrote: 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. pgarcia wrote: Awesome!! Now I just have one more thing, and I will be done with spread sheet. Do you anything about "sum"? "Dave Peterson" wrote: 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 wrote: 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" wrote: 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 wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| excel 2007 autofilter change to 2003 autofilter functionality? | Excel Discussion (Misc queries) | |||
| 2007 excel autofilter back to 2003 autofilter? | Excel Discussion (Misc queries) | |||
| 2007 excel autofilter change back to 2003 autofilter? | Excel Discussion (Misc queries) | |||
| 2007 Autofilter worse than 2003 Autofilter | Excel Discussion (Misc queries) | |||
| How to Sort within AutoFilter with Protection on (and AutoFilter . | Excel Discussion (Misc queries) | |||
Linear Mode
