VBA Clear Contents ONLY

[rob1963manwork]

Hello!

I have a sheet in a workbook called "Backend_Data"

The data to be scrutinized begins in Row # 7, Column A. (see attached workbook)

• I need a VBA Code that will find all 0 in range("A7:A42") and if it finds a zero there, then clear.contents of that row up to Column E.

PS: Deleting the row is not an option.

This is only a sample file that i have attached. The real file is approximately 400K rows down, and 80 columns wide.

Would someone be kind enough to help?

Thank you!!

[maniacb]

maybe something like this

Please Login or Register  to view this content.

[Rick Rothstein]

Does this do what you want...

Please Login or Register  to view this content.

Note: This code assumes there will be at least one cell in Column A of your data with a 0 in it. If that might not be the case, then you will have to add an On Error trap as SpecialCells will error out if it cannot find what it is looking for.

[rob1963manwork]

To Rick and Maniacb

Thanks guys! both worked like a charm!

There will never be a danger of no zeroes in column A. I have a macro that pulls in data from another sheet with 40K rows, and over 90% of them will always be 0


Quick question to Rick: Your code worked perfectly but how did it know to sort the data afterwards? The zeroes were not consecutive at the bottom.


Thank you!!!!

[Rick Rothstein]

Quick question to Rick: Your code worked perfectly but how did it know to sort the data afterwards? The zeroes were not consecutive at the bottom.

I don't understand your question. My code is not sorting anything. All it does is mark all of the cells with 0 as #N/A and then the SpecialCells function locates all of those #N/A cells, uses the Intersect function to extend those located cells by five columns and then does a ClearContents on all of those cells. That's it... it does nothing else.

[rob1963manwork]

Hi Rick..

It's working properly but here is my question more detailed.

My Data on the sheet [prior to running the code] goes like this:

On Column A before code

123
0
0
123
0

After code

123
123

Shouldn't it have left Blank Rows in between? Instead it looks as though I had sorted it. Which is fine, because i was planning to add a macro element to have leave all blank rows at the bottom but I didn't have to.