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.

Rows(4).ClearContents will clear row 4

If you only want to delete A-J.

Intersect(Range("A:J"), Rows(ActiveCell.Row)).Delete xlShiftUp

Select allOpen in new window

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!

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)
 

Select allOpen in new window

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

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!

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
  

Select allOpen in new window

Can you attach a sample workbook?

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!