Solved

Using VBA in Excel, how do I clear 1 row of data (columns A - J) without deleting the actual row (as I have formatted blank rows that follow and need to keep)?  Cell in Column D needs to be selected.

Posted on 2013-02-05
9
450 Views
Last Modified: 2013-02-06
Also note that as this "row" of data is erased/cleared/deleted, all data rows below it need to move up to keep all the records contiguous.


Current code:
           
            Range("E" & ActiveCell.Row, Selection.End(xlToRight)).Select
            Selection.Delete Shift:=xlUp
            Range("C" & ActiveCell.Row, Selection.End(xlToLeft)).Select
            Selection.Delete Shift:=xlUp
                     
 Thank you!
0
Comment
Question by:AvantGuardDog
  • 4
  • 3
  • 2
9 Comments
 
LVL 46

Expert Comment

by:Martin Liss
ID: 38855880
Rows(4).ClearContents will clear row 4
0
 
LVL 33

Expert Comment

by:Norie
ID: 38856009
If you only want to delete A-J.
Intersect(Range("A:J"), Rows(ActiveCell.Row)).Delete xlShiftUp

Open in new window

0
 

Author Comment

by:AvantGuardDog
ID: 38856094
Hi MartinLiss, this vb macro is tied to a button that a user clicks.  They first place their cursor in column D (ID) and click the button.  The number of rows always changes, and the user can pick any row.  The code I need has to be dynamic this way, not tied to a specific row, or even range of rows, as these will change all the time.  Thank you for you help!  Do you know of a way to do this dynamically? (Please see the rest of my reply to Imnorie, as well) ....

Hi Imnorie, thank you!  However, the code I have currently already uses "Delete" which deletes already formatted rows below (and I don't want that to happen) -- I have formatted rows through row 99, and need to keep them; every time the user uses this macro button to delete with "delete" in the code, we lose a formatted row at the bottom.  Eventually, all rows will be deleted.  No good.  Can't use "delete" in the code, must use something like "ClearContents" to just erase what's in the cells and leave the row alone.  But so far, I have not been able to figure out how to use "ClearContents" properly, in context with the rest of the code and requirements.

Here's the full "Delete" program that I have -- it works, but it deletes completely the formatted rows at the bottom of my user range, which is row 99:

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("Are you sure you want to delete this selected Item? (verify by noting TASK ID in cell " & ActiveCell.Address & ")", vbCritical + vbYesNo, "Confirm!")
           
        If msg = vbYes Then
           
            ActiveSheet.Unprotect
            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 DrawingObjects:=True, Contents:=True, Scenarios:=True
        End If
    Else
        MsgBox "Select a Task ID in column D!", vbInformation, "Incorrect column selected!"
    End If
   
    Application.ScreenUpdating = True
End Sub


NOTE: The two arrows above point to the problem I am speaking of -- I don't want any physical rows deleted, I just want the cell contents cleared, and for the physical formatted blank rows below at the bottom of the range to remain untouched.  As you know, "Selection.Delete" physically deletes the entire row the user is wanting to erase, and the net effect of that is that the deleted row up top where the data is causes the blank but formatted rows below to be removed, one at a time, every time this macro is run. Can't do it this way, need those formatted rows to say the way they are.

Thank you both for whatever you can provide that will work -- I really appreciate it!
0
 
LVL 33

Expert Comment

by:Norie
ID: 38856236
Maybe this which clears A:J of the active row and then copies the rows below up.
Intersect(Range("A:J"), Rows(ActiveCell.Row)).ClearContents

Intersect(Range("A:J"), Rows(Activecell.Row+1 & ":" & Range("A" & Rows.Count).End(xlUp).Row)).Copy Range("A" & ActiveCell.Row)
 

Open in new window

0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 46

Expert Comment

by:Martin Liss
ID: 38856409
If msg = vbYes Then
           
            ActiveSheet.Unprotect
            Range("E" & ActiveCell.Row).ClearContents
    Else
        MsgBox "Select a Task ID in column D!", vbInformation, "Incorrect column selected!"
    End If
0
 

Author Comment

by:AvantGuardDog
ID: 38857139
imnorie, I tried your two new lines of code, thank you! -- the first line with "ClearContents" instead of "Delete" works beautifully to erase the data in the cells in the row the user chooses, and does not delete any rows (as "Delete" had done).

The second line of code, however, the row designed to move up the records that are below the newly erased line -- wreaks havoc and does not work, I'm sorry to say.  It wipes out formatting in a few cells in columns a,b,c, ; doesn't pull the records below up either, unfortunately.

Can " Shift:=xlUp" be applied in some way at this point in the code?  I have no idea how to do that though.


Martin -- I really appreciate your suggestion, thank you also; unfortunately, your code only erases a couple of cells and not the entire row, and there is nothing in your code as yet to bring the rows below the erased row up in order to make all the rows of data contiguous again.

Do you think you might have another method for this that may work for us?


I truly appreciate your efforts!
0
 
LVL 33

Expert Comment

by:Norie
ID: 38857367
What 'havoc' exactly is wreaked?

Perhaps if just values were copied up.
Intersect(Range("A:J"), Rows(Activecell.Row+1 & ":" & Range("A" & Rows.Count).End(xlUp).Row)).Copy 

Range("A" & ActiveCell.Row).PasteSpecial xlPasteValues
  

Open in new window


Can you attach a sample workbook?
0
 

Author Comment

by:AvantGuardDog
ID: 38857495
DECISIONFORMTEST.xlsHi imnorie, thank you!  I attached a file for you to have a look at -- it is the actual user structure I am referring to, a very similar duplicate from my file of the sheet in question.  As you can see, columns a,b,c   are merged cell by cell.  Sometimes there is an order number for the sequence in these cells, 1,2,3,4,5,6,7,8 -- corresponding to the data.  Blank rows underneath are always blank (but formatted).  The number of rows with data is constantly changing.

Unfortunately, the new code you just gave to copy/paste the values up does not work either on these rows.  I attached the file thinking you might like to try it out, and see what happens -- it is very difficult to describe, but things get rather scrambled (that is what I meant by havoc), such as formatting from the header, other formatting dropouts, and with the previous version of the code, a record above was copied creating a duplicate (something to this effect).

I hope having the actual file as I use it and as it is formatted helps!!  I very much appreciate your help, thank you!
0
 
LVL 33

Accepted Solution

by:
Norie earned 500 total points
ID: 38857569
The problem was no data in column A.

Anyway, try this.
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

Open in new window

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

910 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

16 Experts available now in Live!

Get 1:1 Help Now