Solved

Deleting all rows below a certain level

Posted on 2012-03-15
17
268 Views
Last Modified: 2012-08-13
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
Comment
Question by:Bright01
  • 9
  • 8
17 Comments
 
LVL 12

Expert Comment

by:sdwalker
ID: 37725309
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
 

Author Comment

by:Bright01
ID: 37726539
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
 
LVL 12

Expert Comment

by:sdwalker
ID: 37726699
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
 

Author Comment

by:Bright01
ID: 37726727
Yes.   One will remain above row 6.  If it's not doable, I understand.

B.
0
 

Author Comment

by:Bright01
ID: 37728783
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
 
LVL 12

Expert Comment

by:sdwalker
ID: 37728809
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
 

Author Comment

by:Bright01
ID: 37728857
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
 
LVL 12

Expert Comment

by:sdwalker
ID: 37728880
See if you can dim shp as Shape or Object. I'm not at a computer just now.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 12

Expert Comment

by:sdwalker
ID: 37732955
Did this work?
0
 

Author Comment

by:Bright01
ID: 37734807
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
 
LVL 12

Expert Comment

by:sdwalker
ID: 37734817
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
 

Author Comment

by:Bright01
ID: 37734851
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
 
LVL 12

Expert Comment

by:sdwalker
ID: 37734864
At church right now. Will try to remember to look at it when I get home.
0
 

Author Comment

by:Bright01
ID: 37762667
sdwalker,

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

Thank you,

b.
0
 
LVL 12

Accepted Solution

by:
sdwalker earned 500 total points
ID: 37763716
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
 
LVL 12

Expert Comment

by:sdwalker
ID: 37796481
Any update on whether the change worked?
0
 

Author Closing Comment

by:Bright01
ID: 37796568
Sdwalker!

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

Much thanks,

B.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

747 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

18 Experts available now in Live!

Get 1:1 Help Now