There has got to be a better way
Pete,
Is this good enough for you
Sub ClearPRLine()
Dim sRange As String
Dim iStart As Long, iEnd As Long
Select Case ActiveCell.Row
Case 10, 11
ClearCells 10, 11
Case 15, 16
ClearCells 15, 16
Case 20, 21
ClearCells 20, 21
Case 25, 26
ClearCells 25, 26
Case 35, 36
ClearCells 35, 36
Case 40, 41
ClearCells 40, 41
End Select
End Sub
Private Sub ClearCells(start As Long, Finish As Long)
Dim sRange As String
'Clear the contents of columns C,D F,G I,J L,M O,P
'now clear the contents of row " & Finish & " columns E, H, K, N, Q
sRange = "C" & start & ":D" & Finish & ",F" & start & ":G" & Finish & _
",I" & start & ":J" & Finish & ",L" & start & ":M" & Finish & _
",O" & start & ":P" & Finish & ","
sRange = sRange & "E" & Finish & ",H" & Finish & ",K" & Finish & ",N" &
Finish & ",Q" & Finish & ",M" & Finish + 2
Range(sRange).ClearContents
End Sub
--
HTH
Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
"Pete" wrote in message
...
There has got to be a better way to do this. But I can not figure it out.
This procedure works but it seems to be very long.
I thought that seting a variable might help but not sure how to do it.
Sub ClearPRLine()
'user selects row
'
'MsgBox ActiveCell.Row
Select Case ActiveCell.Row
Case 10, 11 ' if user selected one of these rows
'Rows 10 and 11 clear the contents of columns C,D F,G I,J L,M O,P
Range("C10:D11").Select: Selection.ClearContents
Range("F10:G11").Select: Selection.ClearContents
Range("I10:J11").Select: Selection.ClearContents
Range("L10:M11").Select: Selection.ClearContents
Range("O10:P11").Select: Selection.ClearContents
'now clear the contents of row 11 columns E, H, K, N, Q
Range("E11").Select: Selection.ClearContents
Range("H11").Select: Selection.ClearContents
Range("K11").Select: Selection.ClearContents
Range("N11").Select: Selection.ClearContents
Range("Q11").Select: Selection.ClearContents
'finally clear the contents of row 13 column M
Range("M13").Select: Selection.ClearContents
Case 15, 16
'Rows 15 and 16 clear the contents of columns C,D F,G I,J L,M O,P
Range("C15:D16").Select: Selection.ClearContents
Range("F15:G16").Select: Selection.ClearContents
Range("I15:J16").Select: Selection.ClearContents
Range("L15:M16").Select: Selection.ClearContents
Range("O15:P16").Select: Selection.ClearContents
'now clear the contents of row 11 columns E, H, K, N, Q
Range("E16").Select: Selection.ClearContents
Range("H16").Select: Selection.ClearContents
Range("K16").Select: Selection.ClearContents
Range("N16").Select: Selection.ClearContents
Range("Q16").Select: Selection.ClearContents
'finally clear the contents of row 13 column M
Range("M18").Select: Selection.ClearContents
Case 20, 21
'same as above but rows 20 and 21
Case 25, 26
'ditto
Case 35, 36
'ditto
Case 40, 41
'ditto
End Select
End Sub
|