Conditional deletion of rows

[AussieExcelUser]

I need help on a macro (actually part of a master formatting macro) t
delete entire rows where the value in a cell in the row equals or i
less than zero. The reference cell is always in the same column.

Put simply, I need the macro to perform the "ctrl -" that I would d
manually.

Any help would be great

 

[Executor]

Hi AussieExcelUser

You can try this:


Sub DeleteRows()
Dim BeginRow As Integer
Dim EndRow As Integer
Dim LoopRow As Integer
Dim chkCol As Integer

BeginRow = 1
EndRow = ActiveSheet.UsedRange.Rows.Count ' To check all lines
chkCol = 6 ' The column with the values to check

For LoopRow = BeginRow To EndRow
If Not IsEmpty(Cells(LoopRow, chkCol)) Then
If Cells(LoopRow, chkCol).Value < 0 Then
Cells(LoopRow, chkCol).EntireRow.Delete = True
End If
End If
Next LoopRow
End Sub


HTH,

RadarEye.

 

[ward376]

see -

http://www.ozgrid.com/VBA/VBACode.htm

 

[Norman Jones]

Hi AussieExcelUser,

Try:

'================>>
Public Sub TesterX()
Dim WB As Workbook
Dim SH As Worksheet
Dim Rng As Range
Dim rCell As Range
Dim delRng As Range
Dim CalcMode As Long
Const col As String = "C" '<<===== CHANGE

Set WB = ActiveWorkbook '<<===== CHANGE
Set SH = WB.Sheets("Sheet1") '<<===== CHANGE

With SH
Set Rng = Intersect(.Columns(col), .UsedRange)
End With
On Error GoTo XIT

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

For Each rCell In Rng.Cells
With rCell
If .Value <= 0 Then
If delRng Is Nothing Then
Set delRng = rCell
Else
Set delRng = Union(rCell, delRng)
End If
End If
End With
Next rCell

If Not delRng Is Nothing Then
delRng.EntireRow.Delete
End If

XIT:
With Application
.Calculation = CalcMode
.ScreenUpdating = True
End With

End Sub
'<<================



---
Regards,
Norman



"AussieExcelUser"

 

[AussieExcelUser]

My thanks to all who responded, I now have my macro working prefectl
and have cut some more hours of reporting effort for my clients.

Cheers