PDA

View Full Version : Less than or equal to code



rider
10-15-2013, 01:35 PM
Hello All,

Greetings... I am working on a small macro. I have some values in the column D includes negative and positive numbers. I tried to write a loop function which will replace the negative and zeros in the column D with "Delete Line" and another loop function will delete the lines which has the word "Delete Line". But i am afraid i am missing something in here, as it is not working, neither giving any error. Below is the code, attached is the file i am working on... Any help is greatly appreciated...

Thanks in advance...

AG



Sub Z020_mod()

' Find the last line and put "Last Line" into the last cell on Column B


Sheets(1).Activate
Last_Row_No = ActiveCell.SpecialCells(xlCellTypeLastCell).Row
Range("C" & Last_Row_No + 1).Value = "Last Row"

Last_Col_No = ActiveCell.SpecialCells(xlCellTypeLastCell).Column
Cells(1, (Last_Col_No + 1)).Value = "Last Col"


Dim Quantity As Integer


n = 2

Quantity = Range("D" & n).Value

Range("D" & n).Activate

Do Until ActiveCell.Value = "Last Row"


If Quantity <= 0 Then
Range("D" & n).Value = "Delete Line"
End If
n = n + 1
ActiveSheet.Cells(ActiveCell.Row + 1, ActiveCell.Column + 0).Select
Loop


'Delete line, if Cell D is "Delete Line"

Range("D2").Select
n = 0
Do Until ActiveCell.Value = "Last Row"
If ActiveCell.Value = "Delete Line" Then
n = 0
Selection.EntireRow.Delete
Else
n = 1
ActiveSheet.Cells(ActiveCell.Row + n, ActiveCell.Column + 0).Select
End If
Loop




End Sub

SamT
10-15-2013, 06:19 PM
Sub SamT()
Dim i As Long
With Sheet1
For i = Cells(Rows.Count, 4).End(xlUp).Row To 2 Step -1
If Cells(i, 4).Value <= 0 Then Rows(i).Delete
Next i
End With
End Sub


With Comments

Sub SamT()
Dim i As Long

'Sheets(1) is an ambiguous assignment
'Sheets("Sheet1") refers to the sheet Named "Sheet1"
'Sheet1 refers to the Sheet Code Named "Sheet1" regardless of the name on the Sheet Tab

With Sheet1 'Edit to suit

'Start loop at last used row in column D, finish in Row 2.
'Always delete rows from bottom up.

For i = Cells(Rows.Count, 4).End(xlUp).Row To 2 Step -1
If Cells(i, 4).Value <= 0 Then Rows(i).Delete
Next i

End With

End Sub