futher macro question

bigK

New Member
Joined
Aug 7, 2007
Messages
49
I am running a macro that looks in column N and if there is a numeric value then it deletes the entire row

the code is:-

Sub deleterows()
For i = Cells(Rows.Count, "N").End(xlUp).Row To 1 Step -1
If IsNumeric(Cells(i, "N")) Then Rows(i).EntireRow.Delete
Next i
End Sub

but it seems to be deleting rows that have no data in ie should be ignored and left alone

am i missing something .....pls help
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Try

If Worksheetfunciton.IsNumber(Cells(i, "N")) Then Rows(i).EntireRow.Delete
 
Upvote 0
Try

If Worksheetfunciton.IsNumber(Cells(i, "N")) Then Rows(i).EntireRow.Delete

what would i need to do if for example I wanted it to remove any rows that had specific text?
 
Upvote 0
As you have seen, IsNumeric() returns "true" for blank cells (it seems to be more "IsNotNon-Numeric"...). You could test for IsEmpty() which returns "true" for blanks as well as for IsNumeric(). Try
Code:
Sub deleterows() 
For i = Cells(Rows.Count, "N").End(xlUp).Row To 1 Step -1 
If IsNumeric(Cells(i, "N")) And Not IsEmpty(Cells(i, "N")) Then Rows(i).EntireRow.Delete 
Next i 
End Sub
 
Upvote 0
what would i need to do if for example I wanted it to remove any rows that had specific text?

If you are looking for the full cell value (ie, delete "comp" but do not delete computer) you would change your test to
Code:
...
If Cells(i, "N") = "comp" Then Rows(i).EntireRow.Delete
...
if you are trying to delete any text string that includes the specified text (ie, you would delete "computer") then you need to use the InStr function, which returns the location of the first character of the search string within the tested string, and "0" if the searched-for text is not found. If InStr returns a value > 0, then you would consider that a match, and delete the row:
Code:
...
If InStr(Cells(i,"N").value,"comp",1) > 0 Then Rows(i).EntireRow.Delete
...
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,122
Members
448,550
Latest member
CAT RG

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top