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
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

KrisSquare

Board Regular
Joined
Feb 1, 2014
Messages
66
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
 

Goldman1965

New Member
Joined
Jun 9, 2013
Messages
25
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>
 

KrisSquare

Board Regular
Joined
Feb 1, 2014
Messages
66
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,326
Members
414,053
Latest member
Dual Showman

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
Top