Solved

Clearing the Sheet

Posted on 2011-09-23
17
243 Views
Last Modified: 2012-06-21
I have  a very nice app. that the Professionals at EE have helped build.  It needs the ability to "reset" the input cells (A3:E100) so a new model can be built.  When I try to do this with a .clearcontents statement in Module 1, I get an error.  I think it's in conflict with the "auto update" command for Column A which may need to be set to off, then reset to on after the input cells are cleared/reset.

I don't think this is a hard one, but I can't get it to work just right or without error.

Thank you for looking at it.

B.
RASCI-Sheetv71.xlsm
0
Comment
Question by:Bright01
  • 9
  • 8
17 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 36588070
The code you have there now works fine for me.

Before you clear the cells you need:

Application.Enableevents = False

Open in new window


then afterwards:
Application.Enableevents = True

Open in new window

0
 

Author Comment

by:Bright01
ID: 36588105
Rorya,

Thanks!  When I do that I now have two instances of Application.Enablevents = False , then True in the same Sub.  It does clear it without error.  Now after clearing, try to put a number in cell A4; there's the error.  However, if you do it a second time, it's gone......... strange.

B.
0
 

Author Comment

by:Bright01
ID: 36596473
Rorya,

Hope you had a great weekend.  When you get a moment, if you could check out the problem with putting a number in A4? I'd much appreciate it.  No hurry.

Thank you,

B.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 36598588
How exactly have you added the code to clear the table? If you added it to the resetRasciTable sub, then you will clear A4 as soon as you type in it (because the populateRasciTable sub calls the clearRasciTable sub at the start), which will cause you problems.
0
 

Author Comment

by:Bright01
ID: 36598700
Rorya,

Thank you.  I get a Debug error on the line in the Module;

' Advanced filter to copy unique task list to table in H3
    ws.Range("B3:B" & TaskRow).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
        "H3"), Unique:=True

Here's where I have the different code:

In the Sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
   
    Dim myCurrentCell As String
    Dim myAllowFindEmptyCell As Boolean
    myCurrentCell = ActiveCell.Address
    myAllowFindEmptyCell = False
   
    If (UCase(Trim(Range("E2").Value)) = "TRUE") Then
   
        If Target.Column = 1 Or Target.Column = 5 Then
       
            If Target.Offset(1, 0) = "" Then
                myAllowFindEmptyCell = True
            End If
           
            Application.ScreenUpdating = False
       
            Dim LastR As Long
           
            With Me
                LastR = .Cells(.Rows.Count, "a").End(xlUp).Row
                If Not Intersect(Me.Range("a4:a" & LastR), Target) Is Nothing Then
                    With Application
                        .EnableEvents = False
                        .ScreenUpdating = False
                    End With
                    With .Sort
                        .SortFields.Clear
                        .SortFields.Add Key:=Range("A4"), SortOn:=xlSortOnValues, Order:=xlAscending, _
                            DataOption:=xlSortNormal
                        .SortFields.Add Key:=Range("B4"), SortOn:=xlSortOnValues, Order:=xlAscending, _
                            DataOption:=xlSortNormal
                        .SortFields.Add Key:=Range("C4"), SortOn:=xlSortOnValues, Order:=xlAscending, _
                            DataOption:=xlSortNormal
                        .SortFields.Add Key:=Range("D4"), SortOn:=xlSortOnValues, Order:=xlAscending, _
                            DataOption:=xlSortNormal
                        .SortFields.Add Key:=Range("E4"), SortOn:=xlSortOnValues, Order:=xlAscending, _
                            DataOption:=xlSortNormal
                           
                           
                        .SetRange Range("A3:E" & LastR)
                        .Header = xlYes
                        .MatchCase = False
                        .Orientation = xlTopToBottom
                        .SortMethod = xlPinYin
                        .Apply
                    End With
                    With Application
                        .EnableEvents = True
                        .ScreenUpdating = True
                        End With
                End If
            End With
            PopulateRasciTable
            'Target.Select
            Range(myCurrentCell).Select
           
            If (Target.Column = 1 And ActiveCell.Column = 2) And myAllowFindEmptyCell = True Then
                Debug.Print ActiveCell.Value, ActiveCell.Address
                Do While ActiveCell.Value <> ""
                    ActiveCell.Offset(-1, 0).Select
                Loop
            End If
   
            Application.ScreenUpdating = True
           
        End If
   
    End If

End Sub


In the Module:

Sub PopulateRasciTable()
Dim ws As Worksheet
Dim TaskRow As Long
Dim TableRows As Long
Dim TableCols As Long

Set ws = ActiveWorkbook.Worksheets("RASCI")

resetRasciTable

Application.EnableEvents = False


' row number of last task
TaskRow = ws.Cells(Rows.Count, "A").End(xlUp).Row

' Advanced filter to copy unique task list to table in H3
    ws.Range("B3:B" & TaskRow).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
        "H3"), Unique:=True
'Advanced filter to copy unique Roles to helper area starting in F11
    ws.Range("C3:C" & TaskRow).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
        "F3"), Unique:=True
'copy helper area and paste special transpose to table headers
    ws.Range("F4:F" & TaskRow).Copy
    ws.Range("J3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
'clear helper area
    ws.Range("F3:F" & TaskRow).ClearContents
    ws.Range("A2").Select
   
RASCI = "=IFERROR(INDEX($E$4:$E$" & TaskRow & ",MATCH($H4&J$3,INDEX($B$4:$B$" & TaskRow & "&$C$4:$C$" & TaskRow & ",0),0)),"""")"
ws.Range("J4").Formula = RASCI

TableRows = ws.Range("H4", ws.Range("H3").End(xlDown)).Rows.Count
TableCols = ws.Range("J3", ws.Range("J3").End(xlToRight)).Columns.Count

ws.Range("J4", ws.Range("J4").Offset(TableRows - 1, 0)).FillDown
ws.Range("J4", ws.Range("J4").Offset(TableRows - 1, TableCols - 1)).FillRight

Application.EnableEvents = True
End Sub
Sub resetRasciTable()

Dim TableRows As Long
Dim TableCols As Long

Set ws = ActiveWorkbook.Worksheets("RASCI")

Application.EnableEvents = False

TableRows = ws.Range("H4", ws.Range("H3").End(xlDown)).Rows.Count
TableCols = ws.Range("J3", ws.Range("J3").End(xlToRight)).Columns.Count

' row headers
ws.Range("H4", ws.Range("H4").Offset(TableRows - 1, 0)).ClearContents
' column headers
ws.Range("J3", ws.Range("J3").Offset(0, TableCols - 1)).ClearContents
' table body
ws.Range("J4", ws.Range("J4").Offset(TableRows - 1, TableCols - 1)).ClearContents
' Input Clear
Application.EnableEvents = False
ws.Range("A4:E100").ClearContents
Application.EnableEvents = True
Application.EnableEvents = True

End Sub


0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 36598760
That's what I thought - so the situation is as I said. You type in A4, the code clears the whole table range again, and you can't filter just a header row.
Why do you want to clear the input table at that point?
0
 

Author Comment

by:Bright01
ID: 36598773
I want to be able to clear the entire Table (data points) starting in A4 so I can start over with a new table. So, if I "clear contents" or set up a macro for clearing contents, I need to then be able to put new data in beginning in A4.

Make sense?

B.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 36598834
Understood - my query was with when you wanted to do it. I'd have a completely separate macro that is just:
Sub ClearInputs()
On Error Resume Next
Application.EnableEvents = False
ActiveWorkbook.Worksheets("RASCI").Range("A4:E100").ClearContents
Application.EnableEvents = True
End Sub

Open in new window


and call that only from the button on the sheet.
0
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.

 

Author Comment

by:Bright01
ID: 36600797
OK...works for me.  Do I put it in the module or on the worksheet?  And do I just delete my current clearcontents statement?

Much thanks,

B.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 36601522
Yes, add it as a new macro in module1 and assign it to the button. Then revert your code back to what you had originally.
0
 

Author Comment

by:Bright01
ID: 36603050
Rorya,

I put the new code in Module1; When I try to reset the code it resets and then goes into some kind of loop.  Sorry.  I must be missing something here.

I've attached the old file with the new updated code in Module 1.


RASCI-Sheetv72.xlsm
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 36707831
YOu appear to have two identical macros, neither of which is assigned to the button. Try this one.
RASCI-Sheetv72.xlsm
0
 

Author Comment

by:Bright01
ID: 36708201
Rorya,

Unfortunately, it now really has several issues that are making it unusable.

Open RASCI-Sheetv72 that you sent back to me and put a 1 in Column A1. The program goes into a long loop ( I think it has something to do with the "auto update True" selection).  I get a "Fillright Method of Range Class Failure" notice after about 3 minutes.

Let's take this from a business perspective;  What I'm trying to do is with one button (not two), reset the model to reinput Priorities (Critical Path) in A, the Task name in B and so on through E.  As I do that, it shows up in the table in Columns H and over.  In the earlier version, the tasks appear and sort correctly as they are added.  I simply don't have a way to clear the entire model.  Both the clear and reset macros can be combined...... one doesn't need to be cleared without the other.

Does that make sense?  

Thank you very much for hanging in here with me.

B.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 36708272
Yes, makes sense. I've just been comparing the two files since the changes made in the code should not cause that behaviour, and it appears that the problem was there originally, it's just that you had data in the file. (if you manually delete the data, then the original file has the same issues).
I will go through the code and add some checks so that it doesn't try to filter when there is no data!
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 36708366
Try this one.
RASCI-Sheetv72.xlsm
0
 

Author Closing Comment

by:Bright01
ID: 36708498
Rorya,

You "rock".  Thank you very much for hanging in with me on this.  Great work. You've helped take this to the next level.

B.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 36708532
Glad to help. :)

Rory
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

743 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

14 Experts available now in Live!

Get 1:1 Help Now