?
Solved

Excel UserFrom Delete Command Button

Posted on 2011-03-01
5
Medium Priority
?
266 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 10

Expert Comment

by:Christian de Bellefeuille
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 150 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

764 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