Clearing row and columns

  • Thread starter Thread starter Rockee052
  • Start date Start date
R

Rockee052

Hello World,

Yes it's me again... Don't mean to post all day, but I ran into a
problem... How can I clear certain rows and colums.? If the active cell
is B6 then B6 and F6 would be cleared. If B7 is the active cell then B7
and F7 would be cleared, and so on... Same with a different selection;
if L6 is the active cell then L6 and Q6 would be cleared and if L7 is
the active cell then L7 and Q7 would be cleared, and so on... Also,
what would be the best way to trigger this; as an opbutton or
cmdbutton. I can't have both sides cleared at the same time i.e., B6,F7
and L6,Q6. They have to be cleared separately, because only one side
might need clearing...

If more info is needed let me know :)

I know this is probably confusing w/o seeing the worksheet (sorry)

Thanks for any help :)

Rockee Freeman
 
use an offset eg

if Target is the selected cell

Target.Value =""
Target.Offset(0,col_offset).Value =""

where you set the variable col_offset to whatever number
of columns you need, 4 so b7 or (7,2) offsets to (7,6)
which is F7

I'd use the right mouse button...so on the sheet's code
page ( right click the sheet tab & select View Code) add
this:
Private Sub Worksheet_BeforeRightClick(ByVal Target As
Range, Cancel As Boolean)
Dim myOffset As Long
Select Case Target.Column
Case 2
myOffset = 4
Case 12
myOffset = 5
Case Else
End Select
If myOffset > 0 Then
Cancel = True
Target.Value = ""
Target.Offset(0, myOffset).Value = ""
End If
End Sub


Patrick Molloy
Microsoft Excel MVP
 
Back
Top