Clear Contents Using a Command Button

Goldman1965

New Member
Joined
Jun 9, 2013
Messages
25
Hi Guys,
I have two tables in one of my worksheets, where if I click on cell in Row A, using a command button (which I've created), that same active cell along with cell B (immediately to the right) the contents will be automatically cleared. Additionally, I want to achieve the same clear contents command button to clear the contents in an active cell in Row I along with cell J (immediately to its right). So essentially, performing two separate commands using the same command button.

Here's my code which doesn't work as it only clears contents in the adjacent rows I and J even when I click on Row A or B (any help would be greatly appreciated):
Code:
Private Sub CommandButton1_Click()
If MyRow = ActiveCell.Row Then
Range("A" & MyRow & ":B" & MyRow).ClearContents

Else
MyRow = ActiveCell.Row
Range("I" & MyRow & ":J" & MyRow).ClearContents
Application.CutCopyMode = False
End If
End Sub
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
From What I understand, on a button click you want to clear contents of Column A,B,I and J in the active row . For achieving this you can try below code

Private Sub CommandButton1_Click()


MyRow = ActiveCell.Row
Range("A" & MyRow & ":B" & MyRow).ClearContents
Range("I" & MyRow & ":J" & MyRow).ClearContents

End Sub
 
Upvote 0
Hi Kris,
Yes I originally had that, but every time I clicked on a cell in row A, the adjacent cells in B, I and J were all cleared. That's not what I want. If I click on a cell in Row A, I only want the contents in Rows A and B cleared. Equally, if I click on Row I, I only want the contents in Row I and J cleared.

Here's an example of what I'm trying to achieve (Food Item on the left is Row A....Food Item on the right is Row I):

Food ItemQtyServe SizeCalorie ServeFat as a PercentSugar in GramsCarbs in GramsFood ItemQty
Special K fruit and nut1.0040 grams1511.707.826.4Tuna 95g - Coles in Spring Water1.00
Processed Bran - Woolies0.5017.5 grams581.001.66.4Wraps Wholegrain - Wattle Valley1.00
Cup of Coffee & Skim Milk1.001 cup240.105.01.3Salad Assorted1.00
Cranberry Juice - Light0.50125 mills100.042.52.5Cheese Grated - Kraft 80%0.50

<tbody>
</tbody>
 
Upvote 0
Try this:

Private Sub CommandButton1_Click()




MyRow = ActiveCell.Row
MyCol=Activecell.column
if MyCol=1 then
Range("A" & MyRow & ":B" & MyRow).ClearContents
Elseif MyCol=9 then
Range("I" & MyRow & ":J" & MyRow).ClearContents
End if


End Sub
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,569
Latest member
Honeymonster123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top