Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sub to clear range above n below diagonal
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sub to clear range above n below diagonal
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. -- Regards, Tom Ogilvy "Max" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sub to clear range above n below diagonal
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sub to clear range above n below diagonal
»Max« wrote:
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sub to clear range above n below diagonal
Thanks, Marcus. That does it for the first part.
Any thoughts on the crucial flexibility part? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sub to clear range above n below diagonal
»Max« wrote:
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 <. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sub to clear range above n below diagonal
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? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sub to clear range above n below diagonal
»Max« wrote:
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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how can i clear a perticular range using a function...??? | Excel Discussion (Misc queries) | |||
If range is empty, clear other cells | Excel Programming | |||
Clear Contants in a Range Based on a Value | Excel Programming | |||
Clear range of cells in different worksheet | Excel Programming | |||
cells in a diagonal range | Excel Programming |