Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code to Find & AutoFilter last zero occurrence
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code to Find & AutoFilter last zero occurrence
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code to Find & AutoFilter last zero occurrence
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? Patrick Molloy wrote: 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 -- Message posted via http://www.officekb.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code to Find & AutoFilter last zero occurrence
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 Sam 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find last occurrence of the value Yes | Excel Programming | |||
Find Last Occurrence of Number | Excel Discussion (Misc queries) | |||
How to find SECOND occurrence in a string? | Excel Worksheet Functions | |||
Find nth occurrence and replace with ":" | Excel Worksheet Functions | |||
find last occurrence | Excel Worksheet Functions |