Solved

Excel UserFrom Delete Command Button

Posted on 2011-03-01
5
264 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: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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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 will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

730 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