deleting rows
 

Hello:

I have a huge spreadsheet of over 20,000 rows. I want to delete any row
that has a cell that contains the phrase "Ending Balance". Is there a way to
tell Excel to delete such rows, without my having to delete each of the rows
one by one?

If I manually delete these rows, it will take hours.

Thanks!

childofthe1980s

deleting rows
 

Hi childofthe1980s

See
http://www.rondebruin.nl/delete.htm

If you can check one column use the filter example (faster)
If not use the loop like this to check the whole row

Sub Example1()
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long

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

Firstrow = ActiveSheet.UsedRange.Cells(1).Row
Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1
With ActiveSheet
.DisplayPageBreaks = False
For Lrow = Lastrow To Firstrow Step -1
If Application.WorksheetFunction.CountIf(.Rows(Lrow), "Ending Balance") 0 Then .Rows(Lrow).Delete
' Delete each row if the value "Ending Balance" exist in the row (It will look in the whole row)
Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub





--
Regards Ron de Bruin
http://www.rondebruin.nl


"childofthe1980s" wrote in message
...
Hello:

I have a huge spreadsheet of over 20,000 rows. I want to delete any row
that has a cell that contains the phrase "Ending Balance". Is there a way to
tell Excel to delete such rows, without my having to delete each of the rows
one by one?

If I manually delete these rows, it will take hours.

Thanks!

childofthe1980s

deleting rows
 

Jim Thomlinson recently posted code to do this

Sub DeleteUnwanted(ByVal DeleteWord As String)
Dim wks As Worksheet
Dim rngToSearch As Range
Dim rngCurrent As Range
Dim rngDelete As Range
Dim rngFirst As Range

Set wks = Sheets("Sheet1")
Set rngToSearch = wks.Cells

Set rngCurrent = rngToSearch.Find(DeleteWord)
If Not rngCurrent Is Nothing Then
Set rngDelete = rngCurrent
Set rngFirst = rngCurrent
Do
Set rngDelete = Union(rngCurrent, _
rngCurrent.Offset(1,0), rngDelete)
Set rngCurrent = rngToSearch.FindNext(rngCurrent)
Loop Until rngCurrent.Address = rngFirst.Address
rngDelete.EntireRow.Delete
End If
End Sub

You can find similar in the help example for findnext, but Jim had altered
the basic approach to collect all the found cells and delete at once - which
should be faster.

For consistency, you should set all the options for the Find command. Such
as Lookat:=xlPart

expression.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection)


--
Regards,
Tom Ogilvy

"childofthe1980s" wrote in
message ...
Hello:

I have a huge spreadsheet of over 20,000 rows. I want to delete any row
that has a cell that contains the phrase "Ending Balance". Is there a way
to
tell Excel to delete such rows, without my having to delete each of the
rows
one by one?

If I manually delete these rows, it will take hours.

Thanks!

childofthe1980s