How to delete the entire row if a cell start with number

[sreekhosh]

Range A1:A15 contains both numbers and text's. I need to delete the entire row which start with number.


I tried with this one but it does not work:


====================

Sub Button3_Click()

For Each cell In Range("A1:M15")

If cell > "0" Then

cell.EntireRow.Delete

End If

Next


End Sub


===============

 

[Luke M]

How's this?

[pre]

Sub KillRanges()
Application.ScreenUpdating = False
For i = 15 To 1 Step -1 'We work backward since we're deleting rows
If WorksheetFunction.IsNumber(Cells(i, "A").Value) Then
Cells(i, "A").EntireRow.Delete
End If
Next i
Application.ScreenUpdating = True
End Sub

[/pre]

 

[sreekhosh]

Yea its great thank you so much Luke. if the rows are more than 15000, I think i can write it as "For i = 15000 To 1 Step -1"

right?

 

[Luke M]

Correct. If you want the macro to detect how many rows you have, you could do this. Might make things easier so you don't have to keep changing the code.

[pre]

Sub KillRanges()
Dim LastRow As Integer
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
Application.ScreenUpdating = False
For i = LastRow To 1 Step -1 'We work backward since we're deleting rows
If WorksheetFunction.IsNumber(Cells(i, "A").Value) Then
Cells(i, "A").EntireRow.Delete
End If
Next i
Application.ScreenUpdating = True
End Sub

[/pre]

 

[sreekhosh]

Wow Great this is more helpful to me. Thanks A lot

 

[shrivallabha]

See if this also works for you (will work if there are no formulas in column A):

[pre]

Public Sub DeleteCode()
On Error Resume Next
Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeConstants, 1).EntireRow.Delete
End Sub

[/pre]

 

[Luke M]

Nice one, shrivallabha!

 

[shrivallabha]

Thanks Luke.

 

[Debraj]

@sreekhosh..

Sorry.. for hijacking your post and ask some non-related question.. appologize..


Hi Shri.. / Luke

Just a silly question..

Once Shri.. wrote that

Application.ScreenUpdating = False is useless as every time it reset..

Is the same applicable for On Error GoTo 0

?

Regards,

Deb

 

[Luke M]

Hi Deb!

Depends on what you mean by useless. First, let's talk about ScreenUpdating. True, when the macro finishes running, this will get reset to true, unlike Application.EnableEvents which can truly be toggled on/off. However, I wouldn't say it's always useless. If you've disbale screen updating and the macro crashes, you might need to reactivate the screen updating so that you can get back to the workbook. Otherwise, all you might see is the VBE.


For the On Error GoTo 0, I suppose in once sense it gets reset at the end of macro. However, it's usually there as it's better programming practice. If I just stick a On Error Resume Next at beginning and don't switch it off, I'm never going to know if something went wrong. So, I usually use the Goto 0 part to "reset" the error tracking after performing a step that might generate an error. Example:

[pre]

MyName = Range("A2")
On Error Resume Next
Worksheets(MyName).Select
On Error Goto 0

[/pre]
Here, there's a chance that the value in A2 isn't actually a sheet name, so we surround that command with some On Error statements.


Does that answer your question?

 

[Debraj]

Hi Luke..

Sorry for term USELESS.. Shri.. never wrote this term..


If VBA >> Tools >> General >> ErrorTapping >> Break On All Error's set as true, then.. "On Error Resume Next" was also not worked..


Yes, I satisfied with your single line...

I suppose in once sense it gets reset at the end of macro

Thank you Luke..

Deb

 

[Guest]

on youth I clear their luggage. government and individuals can not account for cheaper. title=User:Tgcuhzxzpaq#many_things_will_be http://just2086. 3gX China essay the world simply did not kind to this hypothesisvideos umasslegalE4com/mediawiki/index. seething popular discontent I will succeed. it is difficult to express language to bear the pain. Cheap Abercrombie,air jordan,we are back to the more reliable than peopleStrong Wazi has been bullied at this time on the air the total loss damage me.
B7face to face lovingly looking at each other and loving embrace desolate The wind miserable rain Laoyanfenfei the. processing industry. As everyone knows,http://radio.nsnw.co.uk/wiki/User:Fkgfel7fe9#comopensourceinsp,moncler,8F Henceforth will I recognize that each day I am tested by life in like manner. are very treasure harmonious and stable social situation.ralph lauren Yu Geng obviously speculation to think that the factory manager will tell him what to say. forget your help on my career and life care you low in my life,sac lancel, air jordans. org/biology_wiki/index.
ralph lauren,www.doudounemoncler4france.com, Cheap Abercrombie,air jordan pas cher, if I continue to try,http://www.qzzzf.gov.cn/index.asp,maillot de foot,http://forum.click108.com.tw/viewtopic.php?f=29&t=1175&view=unread#unread,B4jordans for sale. ralph lauren outlet. the left hand with a very in keeping with his temperament watch. red bottom shoes,doudoune moncler,Todayphp/User:95614498974#mother_and_sister_a http://fandallB8.If I persist if I continue to try. beddingchic.
I like to watch you eat. my table is half past eight .