VBA Code to Find & AutoFilter last zero occurrence

[Sam via OfficeKB.com]

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

 

[Patrick Molloy]

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]

Hi Patrick,

Thanks for taking the time to reply - much appreciated.

My VBA knowledge is very, very limited. Not sure how to get your code working
based on my original example code.
What should the variables you use be declared as etc.?
Will I have to manually select the data before running the code?
Will the autofilter still be done within the macro?

so you only want to see the rows where the last zero in each column occurs? Yes
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

Yes, Great!

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

Don't understand - place any value? 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

How will the autofilter be done within macro?

Further assistance would be most appreciated.

Cheers,
Sam

 

[Sam via OfficeKB.com]

Hi All,

Would very much appreciate further assistance.

My numeric data spans 7 columns and many rows: starts in column "K" , row 21.

Using VBA code, I wish to find the last occurrence of zero "0" going across a
row spanning 7 columns (NOT the last zero in individual columns).
The row may not necessarily be the last row of data, so will have to start
search from the bottom (last row) and go up, to find the last row with a zero
or multiple zero's.

Once the row with the last occurrence of zero is found, be it one or multiple
zero's in the row; I then need to autofilter the zero or zero's.

Thanks,
Sam

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)