help with highlight code

[RompStar]

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]

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.

 

[RompStar]

Thanks again Dave for helping me out, I will look at your code with
great interest!

Thank you again for taking time! I am learning a lot from you.

 

[RompStar]

Got a question, my headers start at row 6, because I have other
formulas that measure things above there,
so the first row with data would be row 7.

I acctually have several of these sorts, what I posted is just one of
them and I need to put into colum AI
with each different .autofilter sort 1, 2, 3, 4, so that at the end I
can sort on this helper column and
all my sorts will be in the right sequence.

I noticed that everything is offset by a few rows, how would I fix
that ?

Thanks!

Sorry for not mentioning that in the original post.

 

[RompStar]

I figured that out, thanks for the help!

Saved me a lot of headaches.