VBA for deleting cells containing specific text

**andrewzzz** · 03-25-2013, 05:27 AM

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** · 03-25-2013, 06:29 AM

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

**andrewzzz** · 03-25-2013, 06:32 AM

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** · 03-25-2013, 06:56 AM

Try this code

Option Explicit

Sub clear_text()
Dim i As Long, lrow As Long, j As Long, lcol As Long

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

With Worksheets("Sheet1")
    lrow = .UsedRange.Rows.Count
    For i = 2 To lrow
        lcol = .Range("IV" & i).End(xlToLeft).Column
        For j = 1 To lcol
            If .Cells(i, j).Value = "fits machines:" Then
                .Cells(i, j).Delete shift:=xlUp
            End If
        Next j
    Next i
End With

MsgBox "Done"

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

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

**andrewzzz** · 03-25-2013, 07:27 AM

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?

**Code&Chips** · 03-25-2013, 06:57 AM

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.

**arlu1201** · 03-25-2013, 07:52 AM

Change this line from

If .Cells(i, j).Value = "fits machines:" Then

to

If .Cells(i, j).Value like "fits machines:*" Then

**andrewzzz** · 03-25-2013, 08:42 AM

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?

**abduljaleel.mca** · 03-25-2013, 07:59 AM

Use the below formula


If Left(.Cells(i, j), 13) = "fits machines" Then

**arlu1201** · 03-25-2013, 08:55 AM

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** · 03-25-2013, 12:00 PM

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?

VBA for deleting cells containing specific text

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

VBA for deleting cells containing specific text

Re: VBA for deleting cells containing specific text

Re: VBA for deleting cells containing specific text

Re: VBA for deleting cells containing specific text

Re: VBA for deleting cells containing specific text

Re: VBA for deleting cells containing specific text

Re: VBA for deleting cells containing specific text

Re: VBA for deleting cells containing specific text

Re: VBA for deleting cells containing specific text

Re: VBA for deleting cells containing specific text

Re: VBA for deleting cells containing specific text

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions