Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional deletion of rows
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 -- AussieExcelUse ----------------------------------------------------------------------- AussieExcelUser's Profile: http://www.excelforum.com/member.php...fo&userid=3232 View this thread: http://www.excelforum.com/showthread.php?threadid=55212 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional deletion of rows
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. AussieExcelUser wrote: I need help on a macro (actually part of a master formatting macro) to delete entire rows where the value in a cell in the row equals or is 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 do manually. Any help would be great. -- AussieExcelUser ------------------------------------------------------------------------ AussieExcelUser's Profile: http://www.excelforum.com/member.php...o&userid=32326 View this thread: http://www.excelforum.com/showthread...hreadid=552126 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional deletion of rows
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional deletion of rows
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.29fgqn_1150348202.5762@excelfor um-nospam.com wrote in message news:AussieExcelUser.29fgqn_1150348202.5762@excelf orum-nospam.com... I need help on a macro (actually part of a master formatting macro) to delete entire rows where the value in a cell in the row equals or is 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 do manually. Any help would be great. -- AussieExcelUser ------------------------------------------------------------------------ AussieExcelUser's Profile: http://www.excelforum.com/member.php...o&userid=32326 View this thread: http://www.excelforum.com/showthread...hreadid=552126 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional deletion of rows
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 -- AussieExcelUse ----------------------------------------------------------------------- AussieExcelUser's Profile: http://www.excelforum.com/member.php...fo&userid=3232 View this thread: http://www.excelforum.com/showthread.php?threadid=55212 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional deletion of cell contents | Excel Worksheet Functions | |||
Automatic Deletion of Rows | New Users to Excel | |||
Conditional Row Deletion | Excel Programming | |||
On deletion of rows | Excel Discussion (Misc queries) | |||
Problem with Conditional format deletion | Excel Discussion (Misc queries) |