Sub to clear range above n below diagonal

[Max]

Looking for help with a sub(s) which can assist in clearing the ranges above
& below a "diagonal" running from bottom left to top right.

Say I have a populated range D13:G16
where the diagonal cells from bottom left to top right are D16, E15, F14,
G13

I would like to run a sub to clear the range above the diagonal,
ie clear D15, D14:E14, D13:F13

and another sub/option to clear the range below the diagonal,
ie clear G14, F15:G15, E16:G16

Any insights appreciated, thanks

 

[Tom Ogilvy]

Sub ClearUpperLeft()
Set rng = Range("D13:G16")
rng.Value = 1
ii = 12
jj = 3
For i = 1 To 4
For j = 1 To 4
If i + j < 5 Then
Cells(ii + i, jj + j).ClearContents
End If
Next
Next
End Sub

for lower right, change the condition to i + j > 5

5 is the sum of the two positions on the lower left to upper right diagonal.

 

[Max]

Thanks, Tom.

I hit a couple of issues.

The sub clears the designated "triangular" range, but unfortunately, it also
converts the rest of the original rectangular range to "1"s. These cells
should be left intact (cells could be populated with formulas and values).

I would also need the sub to be flexible, to operate on much larger ranges
which could be selected anywhere on the sheet. I would just select the
rectangular range, then run the sub to clear the upper left or lower right.
Then I would select another range (which may be different in size) somewhere
else, and run it again.

 

[Marcus Schöneborn]

Thanks, Tom.

I hit a couple of issues.

The sub clears the designated "triangular" range, but unfortunately, it also
converts the rest of the original rectangular range to "1"s. These cells
should be left intact (cells could be populated with formulas and values).

Remove the

rng.Value = 1

Looks like it is there accidentally.

 

[Max]

Thanks, Marcus. That does it for the first part.

Any thoughts on the crucial flexibility part?

 

[Marcus Schöneborn]

Thanks, Marcus. That does it for the first part.

Any thoughts on the crucial flexibility part?

I don't have Excel here (I am using Excel only at work, at home I am
running Linux), but as I am using VBA much, chances are good that this
untested macro will work:

Option Explicit
Sub ClearUpperLeft()
Dim n As Integer, i As Integer, j As Integer
With Selection
If .Areas > 1 Then
MsgBox "Sorry, can only work on a single area.", vbCritical, "Error"
Exit Sub
End If
n = IIf(.Rows.Count > .Columns.Count, .Rows.Count, .Columns.Count)
For i = 1 To .Rows.Count
For j = 1 To .Columns.Count
If i + j <= n + 1 Then .Cells(i, j).ClearContents
Next
Next
End With
End Sub

In a non-square range, this will clear a quadrangle, not a triangle, for
example:

A B C D E F G
1 x x x x x x x
2 x x x x x x
3 x x x x x
4 x x x x

A B C
1 x x x
2 x x x
3 x x
4 x

To get the opposite behaviour, so it always clears a triangle, change
the > to a <.

 

[Max]

Marcus, thanks.

Tried it out several times, but kept hitting the same error* at this line:

If .Areas > 1 Then

*Run-time error 450:
Wrong number of arguments or invalid property assignment

How can I proceed?

 

[Marcus Schöneborn]

Marcus, thanks.

Tried it out several times, but kept hitting the same error* at this line:

If .Areas > 1 Then

*Run-time error 450:
Wrong number of arguments or invalid property assignment

How can I proceed?

My mistake. Try

If .Areas.Count > 1

instead. Sorry.

 

[Max]

Fabulous, Marcus ! Many thanks.

As I wanted the diagonal itself to remain intact,
I tinkered with this line:

If i + j <= n + 1 Then .Cells(i, j).ClearContents

tweaked it to:

If i + j < n + 1 Then .Cells(i, j).ClearContents

and voila!, that did it. Similarly for the converse.

These are 2 subs which does the job:

Option Explicit
Sub ClearUpperLeft()
Dim n As Integer, i As Integer, j As Integer
With Selection
If .Areas.Count > 1 Then
MsgBox "Sorry, can only work on a single area.", vbCritical,
"Error"
Exit Sub
End If
n = IIf(.Rows.Count > .Columns.Count, .Rows.Count, .Columns.Count)
For i = 1 To .Rows.Count
For j = 1 To .Columns.Count
If i + j < n + 1 Then .Cells(i, j).ClearContents
Next
Next
End With
End Sub

Sub ClearLowerRight()
Dim n As Integer, i As Integer, j As Integer
With Selection
If .Areas.Count > 1 Then
MsgBox "Sorry, can only work on a single area.", vbCritical,
"Error"
Exit Sub
End If
n = IIf(.Rows.Count > .Columns.Count, .Rows.Count, .Columns.Count)
For i = 1 To .Rows.Count
For j = 1 To .Columns.Count
If i + j > n + 1 Then .Cells(i, j).ClearContents
Next
Next
End With
End Sub