VBA code works in excel, but not on my file

[Jamie]

Having difficulties with two codes:

Option Explicit

Sub CleanUp()
Dim lastRow As Long
lastRow = Cells.SpecialCells(xlCellTypeLastCell).Row

Application.EnableEvents = False
'Keep all rows that have been edited
Range("Z2:Z" & lastRow).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Application.EnableEvents = True
End Sub
Sub CleanUpCols()
Dim lastCol As Long
lastCol = Cells.SpecialCells(xlCellTypeLastCell).Column

Application.EnableEvents = False
'Keep all rows that have been edited
Range("A1", Cells(1, lastCol)).SpecialCells(xlCellTypeBlanks).EntireColumn.Delete
Application.EnableEvents = True
End Sub

The point is to keep all rows that have been edited and to delete all columns that have not been edited (edited as is in -- text being changed, added or deleted). These codes work in excel, but they are not working on my attached template. I am wondering if I am missing some formatting somewhere that is causing problems? Any help is very much appreciated.

 

[Luke M]

Hello again Jamie.
Your attached workbook doesn't seem to have any data or code attached to it. Can you explain further what isn't working?

 

[Jamie]

Hey Luke! I tried reaching back out to you but I haven't heard anything. I used the macros exactly how you gave them to me but they don't seem to be working on this particular excel sheet. It deletes everything whether edited or not. Here's the macro enabled file. . .but if you add some text to a few rows and columns and then run it, hopefully you'll see what I'm trying to explain.

 

[Luke M]

Hi Jamie,
If your review my initial post:
http://chandoo.org/forum/threads/vba-macro-help-needed-please.19972/#post-120373

You'll see that there were 3 parts. The Worksheet_Change event is critical for your requirement, as that is the part that flags which cells got changed. If you put that part in, you should be back in business.
Note that the Worksheet_Change event is stored in the Sheet module, not a regular module.

 

[Jamie]

Oh I see, I didn't understand how an 'event' worked (this is new to me) and didn't realize it would be in a different place. Is it necessary to have the helper column and row? I see where I can modify where they go, but can they be removed or is there something I can add so they don't show? (This is different from the worksheet_open, which I do find helpful if I must have the helper column)

 

[Luke M]

Not a problem, it was easy to miss.
Need to have them at least somewhere, as it is what XL later reads when it does the cleanup. You can certainly move them. You could even make the row/column hidden, as the macro doesn't need cells to be unhidden in order to read/write to them.

If you do move them, make sure to make the appropriate change in both the change event and in the cleanup macro.

 

[Jamie]

Alrighty, cool. Let me play some more and I'll let you know if I run into anymore 'bumps' in the road. It's been a really great macro to work with, and I think I may finally have gotten the hang of it. You've been a huge help!