Solved

Excel UserFrom Delete Command Button

Posted on 2011-03-01
5
260 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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

746 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now