VBA that looks up a string (or part of) value and deletes the cell

Hi guys!

My issue is that I have a spreadsheet with a rather large amount of cells containing different values. (Numbers, text, dates, etc.).

What I need to do is search through all cells in this big data-set (A1:Z1000), and delete everything in all cells that begin with the text-string "Hour Text".

With my limited knowledge of VBA, I've been able to get everything right, except for the "search" part.

Any of you know how to get excel to search through all cells in that given array?

Add a filter to the column, filter each column on contains and that text, then delete the visible rows.

Or add a helper column with a formula of =COUNTIF(A2:Z2,"Hour Text*")>0, and filter that column on TRUE and delete all visible rows.

No VBA required.

Hi Bob

Thanks for the quick reply!

I'm sry. I probably didn't explain myself well enough.

The cells that I need to delete are not orderly placed in a single row or column. But distributed all over the spreadsheet with no apparent pattern.

Hence the need for some function/VBA that goes through each individual cell, and deletes it if it begins with the text "Hour Text". (And the "Hour Text" are only the common beginning of what the cells contain. After that each of the cells contain an 8 digit number that changes with each cell.).

first check simple one:

Please Login or Register  to view this content.

if not works as expected, we will drill the subject down

Originally Posted by gaia

Hi Bob

Thanks for the quick reply!

I'm sry. I probably didn't explain myself well enough.

The cells that I need to delete are not orderly placed in a single row or column. But distributed all over the spreadsheet with no apparent pattern.

Hence the need for some function/VBA that goes through each individual cell, and deletes it if it begins with the text "Hour Text". (And the "Hour Text" are only the common beginning of what the cells contain. After that each of the cells contain an 8 digit number that changes with each cell.).

My response still applies.

Thank you Kaper!

That was amazing. Worked like a charm!

Sry Bob. But as I have to do this on a weekly basis. I would rather not have to do all that stuff manually every single time.

Thx for the help both of u!