asked on

Delete the whole row if an entry in Column D has an underscore as part of the cell entry

Dear Experts:

I need to delete the whole row ...
... an entry in Column D has an underscore (or several ones) as part of the cell entry

I would like to run a macro code to achieve this or alternatively ..

... a conditional formatting solution (if feasible)

Help is much appreciated. Thank you very much in advance.

Regards, Andreas

Steve

To do this "simpy"...

Add an autofilter to the sheet (data > Filter)
Filter column D on values with the underscore in them
Highlight all the visible rows (selecting the row number 2 and then [ctrl]+[down arrow])
Press [ctrl]+[minus on num pad] (this will delete all highlighted rows.
Remove the autofilter

sstampf

Try running this code:
Sub stemp()
Dim rng As Range, rng1 As Range, rng2 As Range
Set rng = Intersect(ActiveSheet.UsedRange, Columns("D:D"))
Set rng2 = Rows("1000000:1000000")
For Each rng1 In rng
If InStr(rng1.Value, "_") Then
Set rng2 = Union(rng2, Range(rng1.Address).EntireRow)
End If
Next rng1
rng2.Delete
MsgBox "Done"
End Sub

SOLUTION

Steve

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sstampf

Mine will be faster though on a bigger data set because it will perform the delete operation only once

Steve

Faster is not always better.. ask my wife :)

Andreas Hermle

ASKER

I am always amazed at the overwhelming support one gets when posting a question.

Thank you very much. I will do some testing and then let you know.

Regards, Andreas

Steve

Here is a nice fast simple method...

Sub Fast_D_delete()
    On Error Resume Next
    Range("$D:$D").AutoFilter Field:=1, Criteria1:="*_*"
    Rows("2:1000000").SpecialCells(xlCellTypeVisible).EntireRow.Delete Shift:=xlUp
    Range("$D:$D").AutoFilter = False
End Sub

Open in new window

Andreas Hermle

ASKER

Hi The_Barman:

I am sorry to tell you that nothing gets deleted when running your code at least on my Machine.

Regards, Andreas

Andreas Hermle

ASKER

Hi sStampf,

I am sorry to tell you that an error message is thrown on ...
... Set rng2 = Rows("1000000:1000000") ...

Runtime Error 13, Type mismatch.

Any idea why?

Regards, Andreas

Martin Liss

In any of the above solutions adding Application.ScreenUpdating = False at the top
and Application.ScreenUpdating = True at the bottom will speed things up.

Steve

Could you post a sample workbook. I cannot understand why none of them are working.

ASKER CERTIFIED SOLUTION

sstampf

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Rory Archibald

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Andreas Hermle

ASKER

Dear all,

Rory was right, it had to do with my running this code on an xls-file on my working place.

But strangely enough running sstampf's code and barman's code on xls-files at my home machine, both codes work just fine.

Thank you very much for your overwhelming and professional support.

Regards, Andreas

Andreas Hermle

ASKER

Dear all,

I am so glad to to be able to turn to you for expert advice. This is terrific!

Thank you very much.