Thank you! Thank you! Thank you!

Sorry for the late response..It takes time for me to understand and test it. Just changing one word Columns to Cells does the job..Coding is like a magic!


PS: There are some blank rows between the range and total row so it's not included to the calculation but if I delete total row, it doesn't give an error any more..

brilliant!Many many thanks again..




Quote Originally Posted by sijpie View Post
If your sheets sometimes do not have the summary row, then amend it as follows. Find this part in the code:
             ' extend range to end of sheet
            lR = wsIn.Cells(Rows.Count, rInp.Column).End(xlUp).Row '  last row, now skip summary
            lR = wsIn.Cells(lR, rInp.Column).End(xlUp).Row '
replace these three lines with:
             ' extend range to end of sheet
            lR = wsIn.Cells(Rows.Count, rInp.Column).End(xlUp).Row '  last row, now skip summary if exists
            If wsIn.Cells(lR, rInp.Column).offset(-1,0) = vbNullstring then  ' there is a summary line, 
                 lR = wsIn.Cells(lR, rInp.Column).End(xlUp).Row 'exclude it
            End If
That is where after finding the last row it checks if it is a summary line (on its own) if so it goes up more to exclude the summary line.


OK, now about finding the 'Z' anywhere on the sheet: Find this code:
             'find the 'Z'
            Set rSrch = wsIn.Columns(iCCC)
and replace it with
             'find the 'Z'
            Set rSrch = wsIn.Cells

You now are saying look in all cells