Solved

Using VBA in Excel, how do I clear all data in 1 row that user will select from within a list of rows, and user can first put cursor on any cell in row to choose - all without deleting the actual row?

Posted on 2013-02-04
14
574 Views
Last Modified: 2013-02-06
I will really appreciate your help with this!  I do not want to delete an entire Excel row out of the spreadsheet -- I need to keep the rows that are already there, so I only want to clear/erase the contents of one row, when necessary.

This is my current VBA macro:

Sub DELETESELECTION()
'
' DELETESELECTION Macro
'

'
    Application.ScreenUpdating = False
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.ClearContents
    Range(Selection, Selection.End(xlToLeft)).Select
    Selection.ClearContents
    Application.ScreenUpdating = True
End Sub

The problem with this macro is the following:

1) The user MUST place the cursor in a cell that is in a particular "ID" column , if they place their cursor anywhere else on that row to be cleared, only part of the row will be erased/cleared, it will not get it all.  The user should be able to place their cursor anywhere on that row, and then click the "Delete" macro button (which I already have set up and working).

2) If the user accidentally places the cursor on the header row by mistake (which is locked and protected) and then clicks the "Delete" macro button, because the header cells are locked and the sheet is protected, the program will "break" and bring up the VB code screens and ask to be debugged, which is obviously no good.  Is there any kind of Error coding that can be placed at the beginning of the entire macro that will just go to Sub End and nothing will happen in this accidental situation?

3) It would be really nice to be able to "move" all the rows of data left beneath the new empty row (once cleared) up one row so all rows can be contiguous again as they were before a row of data was cleared.  However, I would like to keep the same order, not have them sorted -- sorting would put all the blanks at the bottom and that would be desirable, but also rearrange the order of the data, and that would not be desirable.

4) Is there a MsgBox with vbYesNo (or something like that) that can be used whereas when the "Delete" macro button is clicked, a message comes up that tells the user to first place their cursor on the row to be deleted (cleared) -- in case they didn't put their cursor on that row, or anywhere at all --  and then tells them to press Enter to continue.. -- and then lets them click the row they want to clear, and then proceeds with the rest of the macro, clearing that row, moving up the rest of the records below (if any, in case it was the LAST row of data they were clearing), etc.

Thank you!  Solving this problem will be very useful!
0
Comment
Question by:AvantGuardDog
  • 8
  • 6
14 Comments
 
LVL 13

Expert Comment

by:Shanan212
ID: 38852055
Whats the column name of this ID?
Is it on column A, B,C...?
0
 

Author Comment

by:AvantGuardDog
ID: 38852329
Hi Shanan212, the ID is in column D  -- the cells in each row of Columns A, B, & C  are merged together (on each row, for 99 rows, row by row) -- and may or may not be empty when the user clicks the "Delete" macro button.  Columns D through J will all have data in them, for any given number of rows, which will always vary, dynamically per user results. So when the user wants to "delete" (clear) a row of data, the row range will be from column A to Column J.  Column D, the "ID" column, is where the cursor needs to be right now, but I am seeking the ability for that cursor to be -- anywhere -- on that row to be cleared.

I hope this additional information helps -- thank you!
0
 
LVL 13

Accepted Solution

by:
Shanan212 earned 500 total points
ID: 38852360
Try this

Sub DELETESELECTION()

    Dim msg As String
    Application.ScreenUpdating = False
    
    If ActiveCell.Row > 1 And ActiveCell.Column = 4 And Len(ActiveCell.Value) > 0 Then
    
    msg = MsgBox("Do you want to delete contents from cell " & ActiveCell.Address & "?", vbCritical + vbYesNo, "Confirm!")
           
        If msg = vbYes Then
            
            Range("E" & ActiveCell.Row, Selection.End(xlToRight)).Select
            Selection.Delete Shift:=xlUp
            Range("C" & ActiveCell.Row, Selection.End(xlToLeft)).Select
            Selection.Delete Shift:=xlUp
        End If
    Else
        MsgBox "Select a cell populated from column D!", vbInformation, "Incorrect column selected!"
    End If
    
    Application.ScreenUpdating = True
End Sub

Open in new window

0
 

Author Comment

by:AvantGuardDog
ID: 38852487
Thank you Shanan212, this routine ran very nicely, and accomplishes beautifully most of what I would like to happen.  Since your routine still requires the user to keep the cursor in a cell in column D "ID" for row they wish to clear, then I think it might be good to try and achieve point # 4 in my original request (above).  How could we accomplish a such a message that comes up when the "Delete" macro button is first clicked -- to tell the user that they should first put their cursor on the ID of the row they wish to erase -- in a msgbox, then click ok, then they put the cursor on the ID of the row they want to erase, then the rest of the macro runs, erasing that line and moving everything else up.

Also -- point #2 in my original question (above) -- how to keep the program from "breaking" and going to the VBA code screens and asking to Debug if the user puts the cursor on the Header line (where the cells are locked, the worksheet protected) -- and the program then crashes.  How to prevent this.  Is there a way, at the beginning of the program, that if there is an error, the program just goes to Sub End and does nothing? (or brings up another msgbox "Place the cursor on the ID in the row you wish to delete."

Again, thank you Shanan212, so much!
0
 

Author Comment

by:AvantGuardDog
ID: 38852532
Shanan212, please ignore the previous message about points 2 and 4, I found a way around these issues that works beautifully with your solution.  Instead of error checking, I changed "If ActiveCell.Row > 1" to "If ActiveCell.Row > 7" -- as the header row is on row 7, and user data below that.  No need for ON ERROR sort of coding after all.  For other issue, I simply called a msgbox with an instruction to put cursor on ID in row to delete, and ok to proceed, click yes, if not, click no.  That works fine too.

So thank you Shanan212  -- your solution works -- very much appreciated!
0
 
LVL 13

Expert Comment

by:Shanan212
ID: 38852579
Hi Avant,

My macro above acomplishes all of your points including point #4 and #2.

This line asks for confirmation

msg = MsgBox("Do you want to delete contents from cell " & ActiveCell.Address & "?"

This line checks if the user has the cursor somewhere else

If ActiveCell.Row > 1 And ActiveCell.Column = 4 And Len(ActiveCell.Value) > 0 Then
 
First part checks if the row is bigger than 1 (considering 1st row has headers)
2nd part checks if the cursor on column 4 aka 'D'
3rd part checks if  the user has cursor on a ID that is populated and not on an empty cell
0
 
LVL 13

Expert Comment

by:Shanan212
ID: 38852584
aah np :)
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:AvantGuardDog
ID: 38852778
Shanan212 --

I just ran the program with my worksheet protected.  All the user rows' formats are set for 'hidden' but are not 'locked'.  The program is breaking at the following point with the sheet protected:

            Range("E" & ActiveCell.Row, Selection.End(xlToRight)).Select
            Selection.Delete Shift:=xlUp

the vba debugger highlights (in yellow)  'Selection.Delete Shift:=xlUp'

That's where it stops and can't go on.

The error box states:

Run-time error '1004'
Delete method of Range class failed

Any thoughts about why, and what to do?  Thanks...
0
 
LVL 13

Expert Comment

by:Shanan212
ID: 38852808
Easy way to overcome this is when you are locking, check 'delete rows'

If you dont want to let users have that access, then use this code and let me know.

Change the locking password to your password as you wish in both places in this code below
Sub DELETESELECTION()

    Dim msg As String
    Application.ScreenUpdating = False
    

    If ActiveCell.Row > 1 And ActiveCell.Column = 4 And Len(ActiveCell.Value) > 0 Then
    
    msg = MsgBox("Do you want to delete contents from cell " & ActiveCell.Address & "?", vbCritical + vbYesNo, "Confirm!")
           
        If msg = vbYes Then
                If ActiveSheet.ProtectContents = True Then
        ActiveSheet.Unprotect "YOURPASSWORD"
    End If
            Range("E" & ActiveCell.Row, Selection.End(xlToRight)).Select
            Selection.Delete Shift:=xlUp
            Range("C" & ActiveCell.Row, Selection.End(xlToLeft)).Select
            Selection.Delete Shift:=xlUp
       ActiveSheet.protect "YOURPASSWORD"
        End If
    Else
        MsgBox "Select a cell populated from column D!", vbInformation, "Incorrect column selected!"
         
   
End If
    
    Application.ScreenUpdating = True
End Sub

Open in new window

0
 

Author Comment

by:AvantGuardDog
ID: 38852848
Yes, that works beautifully, thank you shanan212 !!
0
 

Author Comment

by:AvantGuardDog
ID: 38852994
Shanan212 -- I just noticed that the code 'Selection.Delete' is deleting my empty (blank but formatted) rows at the bottom of my user record area (through row 99), it is important that all these 99 rows remain pre-formatted. In the actual question I asked, how to accomplish the clearing of contents without -- deleting -- the actual row (for this reason of needing the rows to be pre-formatted, actually for a multitude of reasons).  In the vba code, 'Selection.Delete' is not working for my needs -- is there a way to ClearContents instead?  I tried swapping out 'Selection.Delete' with 'Selection.ClearContents' -- but it didn't work, VB debugger came up instead.  I know there has to be a way to do this.  Thanks, Shanan212 .
0
 

Author Comment

by:AvantGuardDog
ID: 38859895
Shanan212 -- what follows is the solution to the problem I just stated above in my previous comment about a part of your solution which deletes empty rows:

(solution -- written and contributed by Experts Exchange Excel guru imnorie):

Dim rw As Long

        rw = ActiveCell.Row

            Intersect(Range("A:J"), Rows(rw)).Delete

            With Range("A98:J98")
                .Copy
                .Offset(1).PasteSpecial xlPasteFormats
            End With

        Application.CutCopyMode = False

This works beautifully, replacing only this part of your code:

(does not work for this problem):

            Range("E" & ActiveCell.Row, Selection.End(xlToRight)).Select
            Selection.Delete Shift:=xlUp
            Range("C" & ActiveCell.Row, Selection.End(xlToLeft)).Select
            Selection.Delete Shift:=xlUp

 (so replace the section of code that does not work with the above solution)


What happens with the new piece of code is that it does indeed delete the user selected row, but then copies the next-to-last row's formats (row 98) to the last row in the pre-formatted range ( row 99), giving the appearance and net effect of not actually deleted a row.  Before, with your code, blank row 99 disappeared and just became Excel's default-formatted white with all other important and necessary formatting removed as well (row height, merged cells, background colors, font color, size, type, text positioning, etc.) -- the first time the "delete" macro is run.  Subsequent runs of the "delete" macro then eliminated row 98, 97, 96, etc.  Eventually there would be nothing left, no preformatted rows, which was not what I needed for this problem/solution -- though the rest of your solution is great, and works beautifully.

Thank youi Shanan212, your great solution combined with this replacement piece of code works beautifully to solve all problems for my "Delete" vb macro.
0
 
LVL 13

Expert Comment

by:Shanan212
ID: 38859967
Glad you figured it out!

I started like you too when I was learning and figured things out myway. You are on the right path :)

Now, to keep formatting for say 100 rows (as pre-formatted), you can copy the formatting of the 1st row and paste it as such

    Rows("2:2").Select
    Selection.Copy
    Rows("3:100").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False

Open in new window


In this way, even after deleting few rows, the 1st row's format will be pasted to rows 3 through 100th row
0
 

Author Comment

by:AvantGuardDog
ID: 38860066
Thank you very much, Shanan212 !!  I incorporated your new code above into the program, and it too works beautifully.  I really appreciate your help so much!
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
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.

743 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

13 Experts available now in Live!

Get 1:1 Help Now