Separate Buttons To Clear Contents of Specified Cells in each Row

A

Adnandos

New member
Joined
Jun 12, 2018
Messages
25
Reaction score
0
Points
0
Excel Version(s)
365
Hi. I have a data capture spreadsheet that I roll out to data capturers.
They capture information by row, with certain columns being automated and locked.

My spreadsheet follows a uniform structure, makes provision for a large number of rows, and I have 18 columns, 6 of which are automated by way of locked formulas, leaving 12 Data Entry Cells per row.

How can I create a button that will be in column A, of each row, that will only clear the 12 Data Entry Cells in that row?

Just looking for a quicker way for data capturers to clear their rows if they identified numerous problems.

I have a basic understanding of Macros, but only very limited knowledge of VBA.

Any assistance would be appreciated.
 
Adnandos said:
makes provision for a large number of rows<snip>
How can I create a button that will be in column A, of each row
Click to expand...
That's going to be a lot of buttons!
Alternatively, you could let them into your secret: if you right-click on a single cell in column B it will delete those columns in that row.
This of course needs a macro:
Code: 
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
If Target.Cells.Count = 1 Then
  If Not Intersect(Target, Columns(2)) Is Nothing Then    'only responds in column B (column 2).
    Cancel = True
    Intersect(Target.EntireRow, Range("C:C,E:G,I:I,L:R")).ClearContents 'these are the columns it's going to delete.
  End If
End If
End Sub
However you'll have to tweak it to delete the right columns, and you can change which column(s) a right-click in will work.
Currently it only works on one cell/row at a time (as the buttons would have worked) but if you want it could be tweaked so that they could select several cells in column B and right-click them and it could clear all the selected rows' data in one go.
See attached.
 

Attachments

  • ExcelGuru9090.xlsm
    15.6 KB · Views: 13
Thank you for the simple explanation and the attachment which certainly does perform the activity requested.

I'll work on seeing if I can incorporate it into my workbook shortly, if there are any stumbling blocks I'll revert with an attachment.
 
Apologies for the late response, your solution worked perfectly!
 
You must log in or register to reply here.
Back
Top