VBA/Macro help needed please

[Jamie]

I'm stuck on a couple macros and would really appreciate any help. I need two different ones:

1. Delete all columns that have been edited at all (text modification, added, etc.)

2. Delete a specified range of rows that have been edited at all (text modification, added, etc.)

Thank you all in advance! I'm having a terrible time trying to figure out how to get excel to understand that a change has been made to the document.

 

[Luke M]

Welcome to the forum!

I'd use the Worksheet_Change event, and a helper column/row.

For instance, have the Worksheet change event do something like:
Cells(Target.Row,"Z").Value = "X"

Then, when you run the clean-up macro, have it filter/search the helper column for X and delete all the lines.

Rough outline, but should get you started.

 

[Jamie]

Thank you! This site has been so awesome so far. I realized I mis-typed the first request I meant 1. Delete all columns that have NOT been edited at all (text modification, added, etc.)

If you guys could write out example macros that would be more helpful as I'm still fairly new to them and can modify but can't write my own from scratch yet

 

[Luke M]

Here's an example workbook for doing the rows. Note that it does have a workbook_Open event, but it just cleans the helper column for prep.

the 3 parts are the :
Workbook_Open event (optional)
Worksheet_Change event (sets helper values)
CleanUp macro (does the actual work)

 

[Jamie]

Ah sweet. This is really cool. Now, how to go about deleting columns that have not been edited for the whole sheet. I feel like this is even simpler but my search results and trial and error have both been failing me.

 

[Luke M]

It's the same idea, but you would flip things 90 degrees. But, to clarify, are we trying to do both things at the same time (rows and columns).

Updated previous file to now have 2 different macros, for you to review/study.

 

[Jamie]

This works perfect on your file but not at all on mine. I guess it would have been better to have started with my file and also make things a bit easier to explain! Sorry about that...it's attached now. I'm wondering if some sort of formatting is messing up things. Anyways, when it comes to rows I'm only really concerned about rows 10 to 20. If they are edited at all they must stay, if not delete. The rest of the rows on the file I need to keep. For columns, deleting those that haven't been edited should be fine for the whole file.