Solved

Excel UserFrom Delete Command Button

Posted on 2011-03-01
5
263 Views
Last Modified: 2012-05-11
Hi,
I have the following code to delete and entire row - this is attached to a command button called Delete.  However, I don't want to delete the entire row.  

I would only like to clear the row and shift cells up, or delete and shift cells up. The reason being is that I have information to the left of this row that I do not wish to be moved.

I do understand that it could be c.EntireRow.ClearContents but I just can't seem to get this to work.  Maybe I need a coffee break?

I am using Excel 2010 with Windows 7 and have posted the code below.

Many thanks in advance
Private Sub cmbDelete_Click()
    Dim msgResponse As String    'confirm delete
    Application.ScreenUpdating = False
    'get user confirmation
    msgResponse = MsgBox("Do you really want to delete this record?", _
                         vbCritical + vbYesNo, "Delete Entry")
    Select Case msgResponse    'action dependent on response
        Case vbYes
            'c has been selected by Find button
            Set c = ActiveCell
            c.EntireRow.Delete    'remove entry by deleting row
            'restore form settings
            With Me
                .cmbAmend.Enabled = False    'prevent accidental use
                .cmbDelete.Enabled = False    'prevent accidental use
                .cmbAdd.Enabled = True    'restore use
                'clear form
                ClearControls
            End With

        Case vbNo
            Exit Sub    'cancelled
    End Select
    Application.ScreenUpdating = True
End Sub

Open in new window

0
Comment
Question by:mjbdbase
  • 3
5 Comments
 
LVL 10

Expert Comment

by:cdebel
ID: 35014027
It's done by doing something like this...
Sheet1.Range("A4:D5").Delete Shift:=xlUp

in your case, your range is C...
0
 

Author Comment

by:mjbdbase
ID: 35014073
Thank you...but I want to clear the entire row from the active cell across and then shift up.  Would that be:
c.EntireRow.Delete Shift:=xlUp?
0
 
LVL 10

Accepted Solution

by:
Makrini earned 50 total points
ID: 35014079
c.EntireRow.Delete    'remove entry by deleting row

That line will delete the entire row

activerow = Activecell.row
Range("C" & activerow & ":F" & activerow).delete Shift:=xlup

Open in new window


This will find the row of the activecell and delete from C through F on that row (only) and will move the below cells up.  It will not touch anything outside of C through F

(Change C and F to whatever suits)
0
 

Author Comment

by:mjbdbase
ID: 35014412
Thanks so much for that.  I was just about to post that I nutted it as well.  So I'm still awarding because without your original help I wouldn't have "got" it.

I have posted below my solution as well.  Thanks again for the help


Private Sub cmbDelete_Click()
  Dim msgResponse As String    'confirm delete
    Application.ScreenUpdating = False
    'get user confirmation
    msgResponse = MsgBox("Do you really want to delete this record?", _
                         vbCritical + vbYesNo, "Delete Entry")
    Select Case msgResponse    'action dependent on response
        Case vbYes
            'c has been selected by Find button
            Set c = Range(ActiveCell, ActiveCell.Offset(0, 30))
            c.Delete Shift:=xlUp 'remove entry by clearing row and shifting cells up
            'restore form settings
            With Me
                .cmbAmend.Enabled = False    'prevent accidental use
                .cmbDelete.Enabled = False    'prevent accidental use
                .cmbAdd.Enabled = True    'restore use
                'clear form
                ClearControls
            End With
        Case vbNo
            Exit Sub    'cancelled
          End Select
    Application.ScreenUpdating = True
End Sub

Open in new window

0
 

Author Closing Comment

by:mjbdbase
ID: 35014416
Excellent assistance as usual
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question