Select and delete contents below diagonal of cell array

If your (square array) is 6x6 staring from A1, then maybe this.
It deletes the below diagonal but doesn't select.
Do you really want to select? If so, why? Selecting isn't usually useful.

Please Login or Register  to view this content.

Thank you very much for the quick response. You are correct I will not need to select the cells if the contents are just deleted. In addition it would be good to have the diagonal start from the current active cell if that is possible. I have tried a few things but its like trying to learn Russian and I am not very good at Russian. I have several large arrays of formulas that require this to be done and that would save me editing the macro each time.
Thanks again and Best regards.

to start from active cell, try the below
the size of the relevant array (6X6, where n=6) is at the top of the code but you can change this as you like

Please Login or Register  to view this content.

Wow, that works perfectly.
Thank you

Hi Sorry, jumped the gun there. The macro clears all the lower left cells but also removes all formulas within the cells in the upper right above the diagonal too replacing them with just "0". Can the clearing of the lower left cells be done without affecting independent formulas in the remaining cells? Thanks again for your attention

perhaps try slight modification

Please Login or Register  to view this content.

or, if you like, you can use unions and select as you initially requested as well as deleting

Please Login or Register  to view this content.

Thanks again for your efforts, sorry I haven't been able to test your new codes while waiting for my spreadsheet to finish its 23 hr run. Both your codes work perfectly but are too slow for me at ~4 cells per second, I have 19 millions cells that will take nearly a month to complete. What I will do is demonstrate more patience myself and use the diag. select tool to delete my way through them manually, it should only take a few hours. If you are still interested my plan is to work my way down the left column of cells and selecting a diag. that gets shorter by 1 cell each step.& deleting... until I can pick big enough squares to delete large chunks at a time.

hmm,
19 million cells involved, some with formulae.
this problem is getting more interesting
try this code
it allows for 4400x4400 (=19.36 million cells) but can change to whatever you like. took me about 1.4 secs for data without formulae starting from active cell A1, but haven't tested it with formulae

Please Login or Register  to view this content.

Tried it and it appears that nothing happens, I used n=4400 and then just n=10, it sat there processing for a couple of seconds and then...nothing.
Did I miss something? My formulas are 3 deep 'If' statements... nothing more than that but there are a lot of them, actually its not 19 million in one array but across 18 arrays, so its good that n is variable.
Thanks again for your attention.

It's hard to do much more without seeing an example of your data.
Can you post a sample file and try to be as specific as you can as to what you want done with it?

That last code I posted. It starts from the active cell as you requested, so you need to have a cell selected else it won't do anything.

I tested that code by filling a 4400x4400 range on a worksheet, selecting cell A1, and running the code.
All cells below the principal diagonal were deleted.

You can try for yourself. Like run the below testdata code on a blank sheet, making sure cell A1 is the active or selected cell. Then run my Post#11 code.

Doesn't it do just what you asked for? and fast enough?

Please Login or Register  to view this content.

Hi kalak, I've discovered my mistake, see screen shot from my test sheet;
Testr_screenshot.jpg
The first array of 2's with active cell A1 works great, however as you move horizontally away from A1 the start of the diagonal is offset from the active cell by the same amount and of course when I tested it before my active cell was CQ so the deleted cells were way off screen....the formula for my array is "=IF($A6=0,0,IF($H6=$B$7,IF($D$7=4,$D$7,0),0))" copied across and down for 994 cells. Hope this helps.

I'm not sure what your problem is at this point, or indeed if you still have one.

I looked at your screen shot, but it wasn't enlightening.

The code in Post#11 does just as you requested in your thread title, and in subsequent posts.

The code below fills a 994x994 array with your formula. Then running the Post#11 code clears everything below the principal diagonal, as you requested. So, just what is the problem, or is there still a problem?

Please Login or Register  to view this content.

Sorry for my poor explanation. I am not trying to be obscure. The code in post 11 doesn't work unless the active cell is A1. My screenshot is missing an explanation I wrote and omitted, if you look at the highlighted cell J1 you will see the array of cells all around are still there and this shot was taken just after I ran the code in post 11, you will notice the diagonal starts at an offset along the same row as J1 equal to the distance J1 is from A1. Otherwise it works fine. Hope this is clearer.

Jooles,

OK. That was an oversight error by me. Sorry about that.
In this line of the Post#11 code

Please Login or Register  to view this content.

just delete the period (i.e. fullstop or point) immediately before Range.