Trouble with VBA code for Excel.

[Guest]

A colleague wrote vba code and has left town for 2 weeks before testing. I
believe I identified the correct section of code causing the problem however,
I am only learning vba programming. This may be out of scope for this forum
but is someone able to read the code and determine if it appears to be
correct. If yes, I will continue troubleshooting.

The file that is to be manipulated looks like:
A B C
D E
563 ACCOUNT: 4MV159560 SHORT NAME: SMITH
581 ACCOUNT: 4MV177935 SHORT NAME: WACKER
601 ACCOUNT: 4MV202709 SHORT NAME: LAITINEN
661 ACCOUNT: 4MV202709 SHORT NAME: LAITINEN
676 ACCOUNT: 4MV215032 SHORT NAME: HIEB

What the code should do is:
IF Col C for given line matches Col C for the row above, delete the line.
For instance, the row beginnign with 661 (in col A) would be deleted because
Col C in the line above (601 in col A) matches.

The code written is:

Dim wks As Worksheet
Dim rngFound As Range
Dim RngToSearch As Range
Dim EndCell As Range

Set wks = Worksheets(4)
Set RngToSearch = wks.Columns("b")
Set rngFound = RngToSearch.Find(What:="ACCOUNT:", LOOKAT:=xlPart)

Range("a1", rngFound.Offset(-1, 0)).EntireRow.Delete

Cells(Worksheets(4).UsedRange.SpecialCells(xlCellTypeLastCell).Row).Select

Set RngToSearch = wks.Columns("b")
Set rngFound = RngToSearch.Find(What:="ACCOUNT:", LOOKAT:=xlPart,
searchdirection:=xlPrevious)

a = Worksheets(4).UsedRange.SpecialCells(xlCellTypeLastCell).Row

Set EndCell = Cells(a, 2)

Range(rngFound.Offset(1, 0), EndCell).EntireRow.Delete
Range("a1").Select

Range("c1").Select
Selection.CurrentRegion.Sort Key1:=Range("C1"), Order1:=xlAscending,
Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom,
DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal

acctnum = Range("c1").Value

For b = 2 To Worksheets(4).UsedRange.SpecialCells(xlCellTypeLastCell).Row
If Cells(b, 3) = acctnum Then
Rows(b).Delete
b = b - 1
End If
acctnum = Cells(b, 3)
Next b

When the query was run, it looped and finally errored due to no records
retrieved.

Again, if this question is out of scope, please let me know. Thank you.

 

[Bob Phillips]

A bit simpler

Public Sub ProcessData()
Dim i As Long
Dim iLastRow As Long

With ActiveSheet

iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 2 Step -1
If .Cells(i, "C").Value = .Cells(i - 1, "C").Value Then
.Rows(i).Delete
End If
Next i

End With

End Sub

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)