so you only want to see the rows where th elast zero in each column occurs?

I think to do this you your loop per column should start at the last row,
its quicker as the first zero it find will be the last

for rw= lastrow to 2 step -1
if cells(rw,cl)=0 then
'blah
exit for
end if
next

you need to place a value, say "X" in another column
so in my loop blah would be
cells(rw,"A") =- "X"

after running this loop through all columns
for cl = 11 to 17 'starting at k (11)
for rw= lastrow to 2 step -1
if cells(rw,cl)=0 then
cells(rw,10)="X" 'place x in column J
exit for
end if
next ' next row if zero not found yet
next ' do the next column

now just autofilter for X in column J




"Sam via OfficeKB.com" wrote:

Hi All,

I would like VBA code to find the last occurrence of zero ("0") per row
starting in column "K" to "Q". There could be more than one zero in a row.
Once the last occurrence of zero is found per row, I then need to use
autofilter to filter the column(s) containing the last zero occurrence.

My numeric data spans 7 columns and many rows: starts in column "K", row 21.
Can the code below be modified to find instead the last occurrence of zero
per row and then use autofilter to filter the column(s) containing the last
zero occurrence.

Your help is most appreciated.

The code below worked ok to find and filter the last cell / row in each
column with a zero value:

Frange is last cell / row in column
nCol is column
afField is autofilter field

Dim Frange As Range
Dim nCol As Integer
Dim afField As Integer

nCol = 0
afField = 11
Set Frange = Range("K21").Offset(0, nCol).End(xlDown)

Do Until nCol = 7
If Frange = 0 Then
Selection.AutoFilter Field:=afField, Criteria1:=Frange.Value
End If
nCol = nCol + 1
afField = afField + 1
Set Frange = Range("K21").Offset(0, nCol).End(xlDown)
Loop


Thank you,
Sam

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200909/1