VBA help: how to delete row if the cell doesnt contain string "*"

[zenzen1]

Hi all,

I am only a beginner in VBA. I have come across the problem where I have a range of data in excel. In each rows, I want to check if the cell in that row does contain a string "*".

If the row where the cell does not contain string "*" I want to delete the whole row.


I am having problem in figure out how to relate each cell search if it contains a string"*".

Can anyone give me some idea/help?

Thanks

ZenZen

 

[SkipVought]

Hi,

Simplest way is to turn on the AutoFilter, and use a Custom criteria to hide rows that you want to keep.

The Select all rows with data and Delete.

Turn on the Macro Recorder if you need VBA code.

Post back with your code if you need help generalizing.

Skip,

_{[red]Be Advised![/red] The Vinyards of Texas have produced a wine with diuretic dimishment and urethric relief...
Pinot More!}

 

[GVF]

Well this is a little tougher than it seems. If you use
For Each DeleteRow in Range(Cells(1,1),Cells(1,10))
and a row gets deleted, DeleteRow skips a row (row 5 becomes row 4 when you delete row 4, and DeleteRow gets moved to row 5 skipping the new row 4).

If you use a do loop like
Do While Activecell.Row <=10
and want the code to run to a particular row, and the last row gets deleted, the code keeps deleting rows and never gets to the row you want to stop at.

This seems to work though...

Sub DeleteRows()
Cells(1, 1).Select
Counter = 0
Do While Counter < 10 'I just picked a number
If InStr(1, ActiveCell.Formula, Chr(42)) = 0 Then
Rows(ActiveCell.Row).Delete
ActiveCell.Offset(-1, 0).Select 'you have to jump back a row to make up for the deleted row
End If
ActiveCell.Offset(1, 0).Select
Counter = Counter + 1
Loop
End Sub

Greg

 

[TonyJollans]

Hi ZenZen,

In this instance, Skip has the right answer (without the need for code). Except that "*" is usualy used to mean "any data" (as Skip has interpreted it) and I suspect that you really want to explicitly find an asterisk (or not).

To find all rows which do NOT contain an asterisk, switch on AutoFilter and select Custom from the dropdown in the column you want to check. Then select "Does not contain" from the dropdown list and enter "~*" (tilde asterisk - without the quotes) in the textbox to the right. Then delete all the visible rows, etc.

Greg,

You have correctly identified and described what happens when deleting members from dynamic collections (such as rows in a worksheet). The way to avoid this is by working from the bottom up when deleting ...

For rowid = lastrow to firstrow step -1
If whatever condition is met in cell(rowid, yourcolumn) Then
Delete the Row
EndIf
Next

Done this way, the deletions never affect the rows which you haven't yet processed.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[