Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Excel UserFrom Delete Command Button

Posted on 2011-03-01
5
Medium Priority
?
267 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

610 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