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
442 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 45

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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 45

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
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…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

705 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

20 Experts available now in Live!

Get 1:1 Help Now