VBA for deleting cells containing specific text

[andrewzzz]

Hi guys,

I was wondering if anybody can give me help me with my question:

I have a very big spreadsheet which I need to clean. I need to delete two types of cells, each containing different text. Let's suppose the first type contains "fits machines:" . I need to have a VBA script which will delete all the cells containing this combination of words, and shift the cell up in each deleted place.

I can do that manually by the following method:

1. Click on Ctrl+F, type there "fits machines:" , click on Find all.
2. In the list found, click on Ctrl+A and select all the cells
3. Click on delete cells
4. From the option popped-up - choose to shift cell up

However, the problem is my computer (icore3 processor) is performing the process very low. Assuming I have over 40000 records to do and seeing that it needs more than two hours to delete and shift up 5000 records, I can not wait that long for the entire process.

I would appreciate your help very much, thank you.

[arlu1201]

Will this string be in one column or anywhere in the file?

[andrewzzz]

Hi Arlu,

It is a file which contains over 1300 columns. Each column has about 30 cells with the text "fits machines:", so it's all over the document. I need the cells containing this text deleted and cells shift up. Do you have any suggestions?

[arlu1201]

Try this code

Please Login or Register  to view this content.

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose View | Macros
Select a macro in the list, and click the Run button

[Code&Chips]

It's a thought, but it may be significantly faster to use a macro and add each cell containing the requisite text (i.e. "fits machine:") to a selection, then do something like Selection.Delete. Try recording a macro of the manual process to see what you can use from that to perform the cell deletion.

This way it only has to rejig the workbook contents once, rather than for each instance of the search text.

[andrewzzz]

Arlu, thank you for your help. The code is perfect, but I assume it works only for cells which contain only the two words "fits machines:", however in my case there are other words after "fits machines:" and in each case it is different. That's why I mentioned that I need a code for cells containing "fits machines:" , but not only these two words. Can you tell me where to adjust the code please?

[arlu1201]

Change this line from

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

[abduljaleel.mca]

Use the below formula

Please Login or Register  to view this content.

[andrewzzz]

Arlu, thank you, it worked well, but one thing - it stopped at column IA , and doesn't perform further. Can this be solved as well please?

[arlu1201]

Regarding the columns, the macro checks the last column in each row and then proceeds. So it will check till the last column of row 2, then go to row 3. So it should work on all your columns.

[andrewzzz]

Arlu, something is indeed wrong and not working, I just tried with another similar document, can we exchange emails so that I can send you the document and you could see it yourself please?

[Hobsons]

I require a command button that will ask various questions such as 'client code' & 'client name'. I need this information to be produced in column a & b on a new row. I also need this row to be inserted alphabetically with all other rows and copy formulas that exist in other rows.

[AB33]

Andrew,
Try this one

Please Login or Register  to view this content.

[AB33]

Hobson,
Please re-start your own thread. It is against forum's rule to post on someone else's post.