LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #9   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


 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how can i clear a perticular range using a function...??? Saleem Hyder Excel Discussion (Misc queries) 1 September 13th 09 07:09 PM
If range is empty, clear other cells Shelly Excel Programming 2 March 28th 07 01:11 AM
Clear Contants in a Range Based on a Value Jeff[_50_] Excel Programming 4 April 14th 06 12:58 AM
Clear range of cells in different worksheet Tim Kelley Excel Programming 1 December 30th 04 06:54 PM
cells in a diagonal range Doug[_8_] Excel Programming 5 November 5th 03 02:55 PM


All times are GMT +1. The time now is 03:17 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"