OK, this might be a bit too difficult to explain on the forum but I thought I would give it a shot.
I have an excel sheet which has 3 columns,
The first and second columns contain different figures and the third column has a figure which could be a negative number (this third column is summed together to give a total)
What I have at the moment is a macro where I can enter a range for column A and a range for Column B and it hides all the rows that don't match the two ranges I put in.
Range 1 (50-100)
Range 2 (1-12)
The macro I have will hide all the rows which have a figure outside 50-100 in the Column A and hide all the rows which have a figure outside 1-12 in Column B - both ranges must be met in order for the row not to be hidden.
Now I am left with a bunch of rows and from this I sum up Column C and get the figure I was looking for.
But what I want is a macro to work out the highest possible sum for Column C and then work out which 2 ranges are needed to get this figure, sort of working backwards to what I am doing now.
I know this sounds pretty difficult to understand but hopefully I explained it ok, I can send on the workbook if needed. Any help greatly appreciated.