Clearing Row Contents (using Intersect?)

[deelee]

I'm back again!

I would be grateful if I could have some help with the following
problem:

I have a spreadsheet that is 306 lines long by 13 coulmns wide. All
cells are 'unprotected' with the exception of columns 'D', 'K' and 'M'
which get their data via fomula from unlocked cells or each other.

What I would like to do is to have a 'button' which will, when pressed,
clear the row of the active cell 'A' i.e' activate a cell in column 'A'
and pressing the 'Clear' button clears the entire adjacent row. (the
ranges would be(""A:C", "E:J", "L")).

I've played around with intersect but can only get the active cell to
clear!

Incidentally, I've a 'Sort' macro which will subsequently move all
empty rows to the bottom of the sheet.

Thanks in advance,

Dave

 

[Leith Ross]

Hello Dave,

Here's the macro. Copy and paste this into a VBA module. You can then
call it from your buttons Click() event.

Public Sub Clear()
Dim Rng As Range
With ActiveSheet
Set Rng = Application.Intersect(.Range("A:C"), .Range("E:J"),
..Range("L"))
End With
Rng.ClearContents
End Sub

Sincerely,
Leith Ross

 

[deelee]

Hi Ross,

Thanks for your quick reply, however, when I use it the routine fails
at the Set Rng statement? I've double checked the ranges and they are
correct.

Thanks again,

Dave

 

[deelee]

Sorry Leith, I used your surname in reply!

Dave

 

[Norman Jones]

Hi Deelee,

Try:

'=============>>
Public Sub Tester()
Dim rng As Range

Set rng = Intersect(Range("A:C,E:J,L:L"), _
ActiveCell.EntireRow)
rng.ClearContents

End Sub
'<<=============

 

[deelee]

Hi Norman and thank you!

It worked a treat - another one for my library!

Dave