Excel Deletes Top to bottom

[Trudye]

Hi Guys, I am trying to delete all the rows preceeding the FIRST ro
that has 'o1CR' in the first 4 bytes of col 'A'. The very first ro
contains a hdr with the literal "AccountNumber" in cell A1.

I have managed to locate the 01CR rec and realize the correct ro
number to start my deletes moving from bottom (the 01CR rec ) to th
top (which I am told is the best way to delete in Excel).

However I have not been successful in assigning the row that proced
the 01CR row as the LastRow. Does anyone know how I do that?

Here is my code. I know there are better techniques but Excel is not m
first language.

Sub CleanUp_File_2()
Dim i As Integer
Dim MaxElements As Integer
Dim cnt As Integer
Dim LeftValue As String
Dim HDR As Range
Dim row1 As Variant

LeftValue = ""
cnt = 0
MaxElements = 7000
ActiveCell.Range("A2").Select

For i = 1 To MaxElements
LeftValue = Left(Cells(i, 1), 11)
If Left(Cells(i, 1), 4) = "01CR" Then
Exit For
Else
cnt = cnt + 1
End If

If cnt = 4379 Then
Stop
End If

Next i

Range("A2").Select
ActiveCell.FormulaR1C1 = "HDR"

Dim LastRow As Range
cnt = cnt - 1
row1 = ("A" & cnt)

LastRow = Worksheets("sheet1").Cells(cnt, 0)
For i = LastRow To HDR Step 1 - 1
If Cells(i, 0) = "AccountNumber" Then
Else
Rows(i).Delete
End If
Next i

End Sub

The error msg appication defines or object defined error becaus
.Cells(cnt, 0)
is invalid. How can I fix this

 

[Dave Peterson]

So you want to keep row 1 and delete rows 2 to the first cell that contains 01CR
as the first 4 characters, but keep that 01CR row?

Option Explicit
Sub testme()
Dim FoundCell As Range
Dim WhatToFind As String

WhatToFind = "01CR*"

With Worksheets("sheet1")
With .Range("a:a")
Set FoundCell = .Cells.Find(what:=WhatToFind, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)

If FoundCell Is Nothing Then
MsgBox WhatToFind & " wasn't found"
Else
If FoundCell.Row < 3 Then
'do nothing, it's already cleaned up
Else
.Range("a2:A" & FoundCell.Row - 1).EntireRow.Delete
End If
End If
End With
End With

End Sub


It's essentially going to the bottom of column A and doing Edit|find and looking
for the topmost cell with 01CR in it.

Then deleting all the rows from 2 to the one above it.

 

[JMB]

Just wanted to offer one or two pointers on your code (for future reference -
my solution is similar to Dave's so I won't repeat what's already done)

LastRow = Worksheets("sheet1").Cells(cnt, 0)

won't work because there is no column 0. Also, you declared Lastrow as a
range, so you must use a Set statement.

Set LastRow = Worksheets("sheet1").Cells(cnt, 1)

Once you know the first and last rows you can delete the entire range
without looping backwards (which I personally hate because it is much slower
to delete one row at a time).

One last thing - I wasn't clear on if you wanted to delete the entire row or
not. If you don't you should be able to change this line in Dave's macro
from:

..Range("a2:A" & FoundCell.Row - 1).EntireRow.Delete

To

..Range("a2:A" & FoundCell.Row - 1).Rows.Delete (xlup)