Another For Ron
 

Hey Ron, you have always given me the right answer, so I'll ask yo
this, as nobody has responded to my last few posts.
I need to make a script that goes through a sheet and deletes every ro
where column J of that row contains a value that is not a number, or i
it is a number and less than or equal to 0...Any help is appreciated

--
bm446
-----------------------------------------------------------------------
bm4466's Profile: http://www.excelforum.com/member.php...fo&userid=3394
View this thread: http://www.excelforum.com/showthread.php?threadid=56835

Another For Ron
 

Here's a quick fix. It could be made faster, but it's an idea to start from.
It also depends on whether you plan to have blanks in your range. if you
have a very long list of data, it would also be advised to group ranges
before deleting them. anyways good luck with this.

Sub test()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

Dim i As Long
i = 1
While Cells(i, 1) < ""
cont:
If Not IsNumeric(Cells(i, 1)) Or Cells(i, 1).Value <= 0 Then
Rows(i).Delete
GoTo cont
Else: i = i + 1
End If
Wend
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

cheers,
Scott
"bm4466" wrote:


Hey Ron, you have always given me the right answer, so I'll ask you
this, as nobody has responded to my last few posts.
I need to make a script that goes through a sheet and deletes every row
where column J of that row contains a value that is not a number, or if
it is a number and less than or equal to 0...Any help is appreciated.


--
bm4466
------------------------------------------------------------------------
bm4466's Profile: http://www.excelforum.com/member.php...o&userid=33949
View this thread: http://www.excelforum.com/showthread...hreadid=568355

Another For Ron
 

Hi bm4466

You can use this example for row 1 - 100 on the activesheet in column A
Change all A to J

See also
http://www.rondebruin.nl/delete.htm

Sub Example2()
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long
Dim StartRow As Long
Dim EndRow As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

With ActiveSheet
.DisplayPageBreaks = False
StartRow = 1
EndRow = 100

For Lrow = EndRow To StartRow Step -1

If IsError(.Cells(Lrow, "A").Value) Then
'Do nothing, This avoid a error if there is a error in the cell

Else
If IsNumeric(.Cells(Lrow, "A").Value) And .Cells(Lrow, "A").Value 0 Then
'do nothing
Else
.Rows(Lrow).Delete
'This will delete each row with the Value "ron" in Column A, case sensitive.
End If
End If
Next
End With

ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



"bm4466" wrote in message
...

Hey Ron, you have always given me the right answer, so I'll ask you
this, as nobody has responded to my last few posts.
I need to make a script that goes through a sheet and deletes every row
where column J of that row contains a value that is not a number, or if
it is a number and less than or equal to 0...Any help is appreciated.


--
bm4466
------------------------------------------------------------------------
bm4466's Profile: http://www.excelforum.com/member.php...o&userid=33949
View this thread: http://www.excelforum.com/showthread...hreadid=568355

Another For Ron
 

One last question for ya...I appreciate all the help.
I am taking a bunch of sheets and merging them into one...but I am left
with formulas which when they are taken out of their original sheet and
moved into the new one, do not work. Is there any way to make all
numbers become permanent numbers and take all the formulas out of the
sheet?


--
bm4466
------------------------------------------------------------------------
bm4466's Profile: http://www.excelforum.com/member.php...o&userid=33949
View this thread: http://www.excelforum.com/showthread...hreadid=568355

Another For Ron
 

See examples here for sheets and workbooks
http://www.rondebruin.nl/tips.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl



"bm4466" wrote in message
...

One last question for ya...I appreciate all the help.
I am taking a bunch of sheets and merging them into one...but I am left
with formulas which when they are taken out of their original sheet and
moved into the new one, do not work. Is there any way to make all
numbers become permanent numbers and take all the formulas out of the
sheet?


--
bm4466
------------------------------------------------------------------------
bm4466's Profile: http://www.excelforum.com/member.php...o&userid=33949
View this thread: http://www.excelforum.com/showthread...hreadid=568355

Another For Ron
 

I see your last formula, looks like it will work great, but where in
here does it delete the row that is less than zero?


--
bm4466
------------------------------------------------------------------------
bm4466's Profile: http://www.excelforum.com/member.php...o&userid=33949
View this thread: http://www.excelforum.com/showthread...hreadid=568355

Another For Ron
 

I see your last formula ?

More details please
What do you mean with last formule


--
Regards Ron de Bruin
http://www.rondebruin.nl



"bm4466" wrote in message
...

I see your last formula, looks like it will work great, but where in
here does it delete the row that is less than zero?


--
bm4466
------------------------------------------------------------------------
bm4466's Profile: http://www.excelforum.com/member.php...o&userid=33949
View this thread: http://www.excelforum.com/showthread...hreadid=568355