Clearing Row Contents (using Intersect?)
 

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


--
deelee
------------------------------------------------------------------------
deelee's Profile: http://www.excelforum.com/member.php...o&userid=34866
View this thread: http://www.excelforum.com/showthread...hreadid=555327

Clearing Row Contents (using Intersect?)
 

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


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=555327

Clearing Row Contents (using Intersect?)
 

:( 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
------------------------------------------------------------------------
deelee's Profile: http://www.excelforum.com/member.php...o&userid=34866
View this thread: http://www.excelforum.com/showthread...hreadid=555327

Clearing Row Contents (using Intersect?)
 

:eek: Sorry Leith, I used your surname in reply!

Dave


--
deelee
------------------------------------------------------------------------
deelee's Profile: http://www.excelforum.com/member.php...o&userid=34866
View this thread: http://www.excelforum.com/showthread...hreadid=555327

Clearing Row Contents (using Intersect?)
 

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
'<<=============


---
Regards,
Norman



"deelee" wrote in
message ...

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


--
deelee
------------------------------------------------------------------------
deelee's Profile:
http://www.excelforum.com/member.php...o&userid=34866
View this thread: http://www.excelforum.com/showthread...hreadid=555327

Clearing Row Contents (using Intersect?)
 

;) Hi Norman and thank you!

It worked a treat - another one for my library!

Dave


--
deelee
------------------------------------------------------------------------
deelee's Profile: http://www.excelforum.com/member.php...o&userid=34866
View this thread: http://www.excelforum.com/showthread...hreadid=555327