Results 1 to 4 of 4

Thread: Separate Buttons To Clear Contents of Specified Cells in each Row

  1. #1
    Acolyte Adnandos's Avatar
    Join Date
    Jun 2018
    Posts
    25
    Articles
    0
    Excel Version
    365

    Separate Buttons To Clear Contents of Specified Cells in each Row



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,595
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by Adnandos View Post
    makes provision for a large number of rows<snip>
    How can I create a button that will be in column A, of each row
    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.
    Attached Files Attached Files

  3. #3
    Acolyte Adnandos's Avatar
    Join Date
    Jun 2018
    Posts
    25
    Articles
    0
    Excel Version
    365
    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.

  4. #4
    Acolyte Adnandos's Avatar
    Join Date
    Jun 2018
    Posts
    25
    Articles
    0
    Excel Version
    365
    Apologies for the late response, your solution worked perfectly!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •