• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 280
  • Last Modified:

Deleting all rows below a certain level

EE Professionals,

I have a simple delete macro that deletes all "unlocked records in a set of rows.

I need the ability to ask the user if they want to clear all data or reset the model.  In the case of clearing all data, I need to expand the range below to include any fields that are not "locked" which I think I can do by simply changing "C3:AD5" to "C3:AD" (do I have to give it a number?).  However, what line(s) of code would I use in the case of a "reset", that would delete all rows below C5?

Here is the current code (In "This Workbook";


Sub ClearWorksheet()

Dim r As Range

For Each r In ActiveSheet.Range("C3:AD5")
'UsedRange
    If Not r.Locked Then
       r.MergeArea.ClearContents
       'r.ClearContents
    End If
Next r
ActiveSheet.Range("AB3") = ""
End Sub

Thank you!

B.
0
Bright01
Asked:
Bright01
  • 9
  • 8
1 Solution
 
sdwalkerCommented:
You do have to provide a number for the row.  I would suggest doing something to find the last row of data, such as the below.

To delete all rows beyond row 5, I would just use
    Rows("6:10000").Select
    Selection.Delete Shift:=xlUp

Open in new window


or

    Rows("6:10000").Clear

Open in new window


As far as finding and deleting down to the last row, I would do ...

Sub ResetWorksheet()

Application.ScreenUpdating = False

Dim r As Range

lastRow = ActiveSheet.Range("C" & Rows.Count).End(xlUp).Row

If lastRow > 2 Then
  For Each r In ActiveSheet.Range("C6:AD" & lastRow)
  'UsedRange
      If Not r.Locked Then
         r.MergeArea.ClearContents
         'r.ClearContents
      End If
  Next r
End If

ActiveSheet.Range("AB3") = ""

Application.ScreenUpdating = True

End Sub

Open in new window


Beware, checking each cell for lock before you delete will get REALLY slow if you have lots of records.

sdwalker
0
 
Bright01Author Commented:
Sdwalker,

Great answer and it works except for one small problem.  With the rows below 6 that I want to delete, I have an embedded graphic.  The rows are deleted but all the graphics remain.  Is there a way to also clear the graphic files below row 6?

Thank you,

B.
0
 
sdwalkerCommented:
Do you have other graphics above row 6? It's difficult to delete graphics based on their location. It would be fairly easy to delete all graphics.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Bright01Author Commented:
Yes.   One will remain above row 6.  If it's not doable, I understand.

B.
0
 
Bright01Author Commented:
sdwalker,

I have an idea here.  The code for resetting the WS is in "This Workbook" so it operates only on the active sheet.  The Chart that must remain is "Chart1".  Is there a way to delete all Charts on a WS, using "This Workbook", except for Chart1?


b.
0
 
sdwalkerCommented:
I'm sorry for not getting back with you sooner (had to go to the funeral home last night for a friend's mom).  What you suggested is the right approach to take.  You simply loop through all the charts (or shapes) and delete everything except, of course, the one(s) you want to keep.  Here's the code for that.

Sub DeleteShapes()

  For Each shp In ActiveSheet.Shapes
    Debug.Print shp.Name ' this line can be deleted
    If shp.Name <> "Chart 1" Then shp.Delete
  Next shp

End Sub

Good luck,

sdwalker
0
 
Bright01Author Commented:
sdwalker,

So sorry to hear about your loss.  Hopefully your friend has strong faith and its nice to have friends like you for support.

Thank you for the code.  When I ran it, I got a error.  I've added a dim statement but still can't get it to compile.  Should I run it as a separate Macro or incorporate it into the major ClearWorksheet macro?

Here's the code I have:

Sub ClearWorksheet()

Dim r As Range

For Each r In ActiveSheet.Range("C3:AD100")
'UsedRange
    If Not r.Locked Then
       r.MergeArea.ClearContents
       'r.ClearContents
    End If
Next r
ActiveSheet.Range("AB3") = ""
Rows("6:10000").Clear
DeleteShapes
End Sub
Sub DeleteShapes()
Dim shp As Range
  For Each shp In ActiveSheet.Shapes
    Debug.Print shp.Name ' this line can be deleted
    If shp.Name <> "Chart 1" Then shp.Delete
  Next shp
0
 
sdwalkerCommented:
See if you can dim shp as Shape or Object. I'm not at a computer just now.
0
 
sdwalkerCommented:
Did this work?
0
 
Bright01Author Commented:
sdwalker,

Thanks for the note.... I tried both dim shape and object.  It did clear the charts....however, it also took out (in both cases), the macro buttons on the sheet.  Is there a category for charts only?

Hope all is well,

B.
0
 
sdwalkerCommented:
No, you'll need to exclude the macro button name as well. If you want to post the spreadsheet, I can help with it.
0
 
Bright01Author Commented:
Here you go.  If you hit clear, you will see it erases the Text (except 3:5) but not the graphic.

B.
Clear.xlsm
0
 
sdwalkerCommented:
At church right now. Will try to remember to look at it when I get home.
0
 
Bright01Author Commented:
sdwalker,

Any update on this?  No hurry....just didn't want the question to go inactive.

Thank you,

b.
0
 
sdwalkerCommented:
Sorry - I forgot I was supposed to look at it.  I thought I was waiting on you.  This should do it.

Thanks,

sdwalker
Clear-sdw.xlsm
0
 
sdwalkerCommented:
Any update on whether the change worked?
0
 
Bright01Author Commented:
Sdwalker!

Great!  I had to uncomment off the object subroutine but then it worked great!

Much thanks,

B.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 9
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now