Microsoft Excel Macro Help

[jackiejoe]

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.
E.G.
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.

Thanks,

[manoshailu]

Hi,

I got some idea from your thread but if you given me that excel file then that could be helpful to me.

Have a nice day
Shailaja :)

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.
E.G.
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.

Thanks,

[jackiejoe]

Hi, thanks for the help I have attached the spreadsheet - basically, there is a macro Ctrl-Shift-M to run it which takes two ranges for Column F and G and it hides all the rows which dont correspond to the inputted ranges, then the Total in Cell M1 adds up all the visible figures in Column I, what I want is something that will work out two ranges which will return the largest Total. Thanks for looking...