View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
Max Max is offline
external usenet poster
 
Posts: 9,221
Default 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