Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|