Excel Macro: If cell value in range is <>"", then clear offset cells

Hi everyone,

Was hoping someone out there might be able to show me more efficient way of writing some simple VBA. Current the execution is time consuming, and the example file I have posted is only a fraction of the entire ranges I need to run the Macro on.

The problem is this:

I want to create a macro whereby, if the cells in column N have values in them (or not blank), the adjacent columns O -> S are cleared. Given I am running the code over multiple spreadsheets and thousands of rows, it is taking approximately 2 mins 30 sec to execute the code. Kinda defeats the purpose of automating the task when I could do it manually in less time.

Very interested to hear your thoughts and suggestions

Here is my current code:

Please Login or Register  to view this content.

Code Tags Added
Your post does not comply with Rule 2 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html



(I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)

Can I suggest that rather than looping through cells which will take a long time you Autofilter the data so that just the cells/rows yuo're interested in are showing - use a helper column if necessary to identofy those rows.

Then use the

Range("your_range").SpecialCells(xlCellTypeVisible)

syntax to identify the cells you want to clear and apply the blank. This requires just one VBA call back to the Excel App to do the job not the many rows that a loop involves