About Us

Thanks again to you both for the help

"Bernie Deitrick" wrote:

John,

While you can modify that code, and have it work, it is much better to sort your data first based on
your deletion criteria. Excel deletes blocks of rows much more quickly than individual rows, which
you will find out when you have a lot of rows, and a lot of rows to be deleted interspersed.

For your problem, try the code below.

HTH,
Bernie
MS Excel MVP

Sub Delete0sInColH()
Dim myRows As Long
Range("A1").EntireColumn.Insert
Range("A6").FormulaR1C1 = _
"=IF(RC[8]=0,""Trash"",""Keep"")"
myRows = ActiveSheet.Range("I65536").End(xlUp).Row
Range("A6").Copy Range("A6:A" & myRows)
With Range(Range("A6"), Range("A6").End(xlDown))
.Copy
.PasteSpecial Paste:=xlValues
End With
Range("A1:A5").Value = "Keep"
Cells.Select
Selection.Sort Key1:=Range("A6"), Order1:=xlAscending
Columns("A:A").Find(What:="Trash", After:=Range("A1")).Select
Range(Selection, Selection.End(xlDown)).EntireRow.Delete
Range("A1").EntireColumn.Delete
End Sub

"John" wrote in message
...
Can this piece of code be adapted easily to delete a row when the active
cell value is less than 0?

Range("h6:h700").Select
Dim rngToSearch As Range
Dim wks As Worksheet
Dim rngFound As Range

Set wks = ActiveSheet
Set rngToSearch = wks.Columns(3)

Set rngFound = rngToSearch.Find("-")
If rngFound Is Nothing Then
MsgBox "No Deletions Found"
Else
Do
rngFound.EntireRow.Delete

Set rngFound = rngToSearch.FindNext
Loop Until rngFound Is Nothing
End If
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
In a protected worksheet allow users to delete rows	Jason Trivett	Excel Worksheet Functions	1	July 12th 05 09:50 AM
How do I find duplicate rows in a list in Excel, and not delete it	Matthew in FL	Excel Discussion (Misc queries)	2	June 15th 05 09:11 PM
Protect Worksheet but allow to insert or delete rows	Bob L Hilliard	Excel Discussion (Misc queries)	2	June 9th 05 02:08 PM
How to delete rows when List toolbar's "delete" isnt highlighted?	Linda	Excel Worksheet Functions	1	May 26th 05 08:39 PM
How to delete blank rows	John Mansfield	Excel Discussion (Misc queries)	3	April 27th 05 11:48 PM

Menu

About Us