?
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
Medium Priority
?
489 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
[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
  • 4
  • 3
  • 2
9 Comments
 
LVL 49

Expert Comment

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

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
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.

 
LVL 34

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
 
LVL 49

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 34

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 34

Accepted Solution

by:
Norie earned 2000 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
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…
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…
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.

770 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