So you're filtering A:AH and sorting A:AH, but shading A:AI?

I would think that if you had data in AI that you would want to filter and sort
that so that it would be associated with the correct row after the sort.

But I think that this does what you asked:

Option Explicit
Sub testme()
Dim wks As Worksheet
Dim lr As Long

Set wks = Worksheets("Sheet1")

With wks

'remove any existing arrows and filters!
.AutoFilterMode = False

'however you set this lr variable
lr = .Cells(.Rows.Count, "A").End(xlUp).Row

With .Range("A6:AH" & lr)
.AutoFilter Field:=29, Criteria1:="=", Operator:=xlAnd
.AutoFilter Field:=34, Criteria1:="X"
End With

With .AutoFilter.Range
If .Columns(1).Cells.SpecialCells(xlCellTypeVisible) _
.Cells.Count = 1 Then
'only the header row is visible, do nothing!
Else
.Sort _
Key1:=.Columns(3), Order1:=xlAscending, _
Key2:=.Columns(4), Order2:=xlAscending, _
Header:=xlYes, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal

With .Resize(.Rows.Count - 1, .Columns.Count + 1).Offset(1, 0) _
.Cells.SpecialCells(xlCellTypeVisible).Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
End If
End With

.AutoFilterMode = False

End With

End Sub

If you wanted to filter, sort and shade A:AI, then try this:


Option Explicit
Sub testme2()
Dim wks As Worksheet
Dim lr As Long

Set wks = Worksheets("Sheet1")

With wks

'remove any existing arrows and filters!
.AutoFilterMode = False

'however you set this lr variable
lr = .Cells(.Rows.Count, "A").End(xlUp).Row

With .Range("A6:AI" & lr)
.AutoFilter Field:=29, Criteria1:="=", Operator:=xlAnd
.AutoFilter Field:=34, Criteria1:="X"
End With

With .AutoFilter.Range
If .Columns(1).Cells.SpecialCells(xlCellTypeVisible) _
.Cells.Count = 1 Then
'only the header row is visible, do nothing!
Else
.Sort _
Key1:=.Columns(3), Order1:=xlAscending, _
Key2:=.Columns(4), Order2:=xlAscending, _
Header:=xlYes, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal

With .Resize(.Rows.Count - 1).Offset(1, 0) _
.Cells.SpecialCells(xlCellTypeVisible).Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
End If
End With

.AutoFilterMode = False

End With

End Sub

=======
The important lines are these:

With .AutoFilter.Range
If .Columns(1).Cells.SpecialCells(xlCellTypeVisible)
.Cells.Count = 1 Then

and
With .Resize(.Rows.Count - 1, .Columns.Count + 1).Offset(1, 0) _
.Cells.SpecialCells(xlCellTypeVisible).Interior

The first looks at the entire range that was just filtered. Then it looks at
the first column of that range and counts the visible cells in that column.

If it's equal to 1, then only the headers are visible.

The second line looks at filtered range, but then resizes it by one less row --
to avoid the header and one more column (AI). But then it offsets that range by
1 row and 0 columns (just the detail records). Then it only looks at the
visible cells in that range.

The second version doesn't need to add a column.




On 08/12/2010 11:40, RompStar wrote:
I am having a problem, this code is supposed to select using
autofilter a blank in column 29 and an "X" in column 34, which it
does. The problem is that sometimes there are no row values left
after these 2 autofilter conditions and it puts a value of 3 and
highlights all the ROWS outside of the .autofilter condition and I
just want it to do that on the rows that survive and not rows that are
outside of these 2 conditions in the worksheet.

How do I tell this code to only highlight the color and the value of 3
into rows that survive the autofilter seletions or skip it if there
are no values ?

Thank you.



With Range("A6:AH"& lr)
On Error Resume Next

.AutoFilter Field:=29, Criteria1:="=", Operator:=xlAnd
.AutoFilter Field:=34, Criteria1:="X"

.Sort Key1:=Range("C7"), Order1:=xlAscending, Key2:=Range( _
"D7"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1,
MatchCase _
:=False, Orientation:=xlTopToBottom,
DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal

Range("AI7:AI"& lr).Value = 3

Range("A7:AH"& lr).Select
With Selection.Interior
.ColorIndex = 36 ' light yellow
.Pattern = xlSolid
End With

.AutoFilter

End With

--
Dave Peterson