Clear, ClearContents, Delete Sometimes Not Working - Command Appears To Be Ignored

[JaredRL]

Hi,
I have some code that is supposed to find the last row and column with data and clear the contents. Normally, this works, but there are circumstances when this code (see step 3) does not clear the contents. I've tried two methods, one that uses a named range and another that creates a range based on the furthest row,column. Both do not work sometimes. I watch the commands being built in the debugger and all the variables are getting set correctly. The command is executed without a run time error, but the cells do not clear. I use Excel 2010, any suggestions would be appreciated.

Sub ClearSave(targetSheet As String, Optional ColumnToSearch As String = "C")
'This code will clear the data in the sheet and prepare for new saved data.

'Steps:
'1. Determine which sheet is being requested to be cleared
'2. Adjust the named range to clear all populated rows
'3. Remove the values

'Declare Variables Needed
Dim RangeToClear As String
Dim LastRowWithData As Long
Dim LastColumnWithData As Long
Dim TargetWorksheet As Worksheet
Set TargetWorksheet = Sheets(targetSheet)

'==================
' Step 1
'==================
'Determine which tab was requested to be cleared
'Set the respective range name to be used to clear the data
Select Case targetSheet
    Case "workingProjectionsDB"
        RangeToClear = "tabClear_WP"
    Case "PGworkingProjectionsDB"
        RangeToClear = "tabClear_PGWP"
    Case "What-If-01"
        RangeToClear = "tabClear_WI01"
    Case "PGWhat-If-01"
        RangeToClear = "tabClear_PGWI01"
    Case "What-If-02"
        RangeToClear = "tabClear_WI02"
    Case "PGWhat-If-02"
        RangeToClear = "tabClear_PGWI02"
    Case "importDataDB"
        RangeToClear = "tabClear_Actual"
    Case "Original"
        RangeToClear = "tabClear_Orig"
    Case "PGOriginal"
        RangeToClear = "tabClear_PGOrig"
    Case "CP-Commit"
        RangeToClear = "tabClear_CP"
    Case "PGCP-Commit"
        RangeToClear = "tabClear_PGCP"
    Case "LP-Commit"
        RangeToClear = "tabClear_LP"
    Case "PGLP-Commit"
        RangeToClear = "tabClear_PGLP"
    Case "LLP-Commit"
        RangeToClear = "tabClear_LLP"
    Case "PGLLP-Commit"
        RangeToClear = "tabClear_PGLLP"
    Case "pgDataDB"
        RangeToClear = "tabClear_PGData"
    Case "allCCDataDB"
        RangeToClear = "tabClear_AllCC"
    Case "mergedCCsDB"
        RangeToClear = "tabClear_Merged"
    Case "userDefaultsDB"
        RangeToClear = "tabClear_UserDef"
    Case Else 'No Tab, or invalid tab indicated
        'Add Error Trap Here
End Select

'==================
' Step 2
'==================
'Adjust the range name to cover the entire data set
'First check to see if there is any data
If Range(RangeToClear).Value <> "" Then 'No data in first row.  Skip the clear.
    LastRowWithData = TargetWorksheet.Cells.Find("*", searchorder:=xlByRows, SearchDirection:=xlPrevious).Row
    LastColumnWithData = TargetWorksheet.Cells.Find("*", searchorder:=xlByRows, SearchDirection:=xlPrevious).Column
Else
    LastRowWithData = 1
    LastColumnWithData = 1
End If

'==================
' Step 3
'==================
'Clear the data
'Research: Sometimes these clear commands don't work.
If LastRowWithData > 1 Then 'there's data.  If value = 1 there's no data, only a header
    Range(RangeToClear).Resize(LastRowWithData, LastColumnWithData).Clear
    Range(TargetWorksheet.Cells(2, 1), TargetWorksheet.Cells(LastRowWithData, LastColumnWithData)).Clear
End If
End Sub

 

[JaredRL]

One additional fact I just learned that might be helpful. This code runs twice for some worksheets. It appears to be a problem on the second pass.

 

[tonyyy]

Jared,

I suspect there may be an issue with how the LasRowWithData and LastColumnWithData are created. Try substituting the following for the two lines in Step 2:

LastRowWithData = TargetWorksheet.Cells.Find("*", searchorder:=xlByRows, SearchDirection:=xlNext).Row
LastColumnWithData = TargetWorksheet.Cells.Find("*", searchorder:=xlByColumns, SearchDirection:=xlNext).Column

As a debugging aid, in Step 3 you might substitute .Clear with .Font.Bold=True, just to visually see what range is being defined.

Cheers,

tonyyy

 

[JaredRL]

Thanks for responding! I used your lines in step 2 and it did not find the last column and last row. Instead it read the last row as 1 and the last column as 1. If I look at the sheet I could clearly see that row should be 214 and the column 20. The original code did capture the right values.

It's a real mystery why step 3 completely ignores the .clear command. The only way I was able to get around this is to set the entire range to a double quote (""). This clears the data and replaces it with "" just fine so I know it's able to read the range correctly.

Also, changing the font and background color is ignored. I even tried activate and select. Those commands are also ignored.

It appears the only thing I can do is read the value of the cells and change the value of the cells.
I added some error traps but no error is lifted.

Again, the .clear command works the first pass. It's when it tries to clear the sheet the second time this occurs.

Jared

 

[DocAElstein]

Hi JaredRL,
. I am not sure if this may be a small help. But I have often encountered problems with .Clear not fully clearing data or formats etc.
. Three things to consider
. ) .Clear does not clear all Formatting, even if everything you try may suggest it does / has cleared everything ( a small change in height of a row is for example one thing that is not cleared that often catches me out.

. 2) If you are confident that your code selects correctly the last used rows, would it be possible rather than to clear the range , to modify your code to delete all those rows ( except for the header row ). Deleting rows seems a much more well behaved way to “Clear” Data

. 3 ) Bear in mind that VBA has a “Memory” of the last Used Range. Some things reset this memory and some things do not! Similarly some things work using the last Used Range and some things automatically update or refresh this memory so that they or following commands looking to find last rows or columns, etc. may work differently. This could explain things working sometimes!! The documentation seems very bad on this.

......

. Otherwise it is very difficult to guess exactly what is going wrong in your File.
. I guess someone may need to see a copy of your file ( preferably with reduced data, and sensitive data removed or changed ). Maybe if you uploaded a file for example send over this free thing: Box Net,
( Remember to select Share after uploading and give us the link they provide. ) , then someone may be able to help further..

Alan

 

[JaredRL]

Thanks for the response. I'm okay with the .clear, clearcontents, and .delete command. Any are suitable for what I'm doing. I just need to make sure there are no values in the cells. But unfortunately none of these commands work the second time this is used.

The intriguing thing about this problem is that I can manipulate the values in the range, so Excel clearly knows the range exists. As a workaround, I calculate the range and set the value to double quote "". It's seems sloppy to code this way but for now it works.

I'd like to learn more about your point #3. Where can I read more about this? Is there a way to reset the memory? This sounds like the problem I'm having.

 

[DocAElstein]

.......
I'd like to learn more about your point #3. Where can I read more about this? Is there a way to reset the memory? This sounds like the problem I'm having.

Hi JaredRL,
. I am not sure if i can help much on that. - I was struggling for a few days to understand that myself. If you have time to read my last post in this Thread
VBA .SpecialCells(xlCellTypeBlanks) – Only works on UsedRange? (Regardless of specified ra
( where i tried to answer my own question!! ) you will see what I mean and maybe from my conclusions get some idea. But you will also see that I did not really get to fully understand it.

. Basically there I was looking at .Specialcells which appeared mostly at its upper limit to be bound by the Last Used Range upper limit. If I manually removed all entries I then had no Used range. However my ,SpecailCells Code still “Worked” remembering the last Used Range.... UNLESS I used certain Properties to obtain the Used Range. Then my code did not work. This was because a By-product of those Properties was to reset that memory. So throwing in one of those Properties at some point in your code ( such as

Dim Number as Long 
Let Number = UsedRange.Row

) - this would force VBA into resetting it’s memory of the Used Range. But really I am making wild guesses here.

. - As you will see from my ramblings at that Thread, there were exceptions to the rules. This all makes this subject a real tricky one.
. If I get any further I will post here further here ... Up until now most of my Googling just gave answers like “VBA” behaves badly or does not handle well or consistently it’s memory of The last Used Range.” Not much help to us!
.

Here was one google:
Excel Developer Tip: Automatically Resetting the Last Cell


Alan.

P.s. Another last “Wild” thought – If you are looking / playing with “empty cells” you may find that looking for “” would give different results as looking for .SpecialCells(xlCellTypeBlanks) – But again this is just throwing in another wild direction for You to be thinking about!!