Sub to clear range above n below diagonal
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
|