Need help with odd behavior of worksheet rows

[Ragnar Midtskogen]

Hello,

I am working on a VB6 application that manipulates Excel Worksheets.

Right now I am working on code to delete rows identified with character 'r'
in column 1, and som of the wrong rows are deleted.
It looks as if Excel does nor recognize that rows have been deleted.

The code shown below is intended to delete the rows marked with 'r' and
leave the unmarked rows.
The sample I am using right now has column headers in row 1, as hown in the
small sample below, only the first 4 columns are shown:
The sheets contains only text, no formulas

Processed UserID
PolicyNumber Defendant
r FD3F55F4-D17D-4467-B45E-4008D295FE00 1212221 Dr.
Jones
r FD3F55F4-D17D-4467-B45E-4008D295FE00 1121222 Dr.
Hayes
r FD3F55F4-D17D-4467-B45E-4008D295FE00
r FD3F55F4-D17D-4467-B45E-4008D295FE00
r FD3F55F4-D17D-4467-B45E-4008D295FE00
FD3F55F4-D17D-4467-B45E-4008D295FE00
FD3F55F4-D17D-4467-B45E-4008D295FE00
FD3F55F4-D17D-4467-B45E-4008D295FE00

The code in question is:

Dim oCommonExcelWorkbook As Excel.WorkBook
Dim oCurrentSheet As Excel.WorkSheet

All variable starting with ln are declared as Long

For lnRowIndex = lnLastSourceRow To 1 Step -1
If (oCurrentSheet.Cells(lnRowIndex, 1) = "Processed") Then
Else
If (oCurrentSheet.Cells(lnRowIndex, 1) = "r") Then
oCurrentSheet.Activate
Range(Cells(lnSourceRow, 1), Cells(lnSourceRow,
lnColumnCount)).Select
Selection.Delete
oCommonExcelWorkbook.Save
End If
End If
Next lnRowIndex

The lnLastSourceRow holds the number of the last row with data in it, in
this case 9.
the lnColumnCount holds the number of columns with data in them, in this
case 15.
I chose to run the loop index backwards to avoid tripping up Excel with
respect to the row numbers, but it appears to get tripped up anyway.

The result of running the code, verified by stepping through, is that rows
9, 8, and 7 are skipped, as should happen, then row number 6 is deleted.
As the index passes theough 5, 4, 3, and 2, the 'r' is detected and the row
is deleted, everything looks fine.
However, at the end of the loop, the sheet, when viewed in Excel, shows that
the original rows 6 through 9 have been deleted, the original rows 1 through
5 are still there.
Am I missing something here?

Any help would be appreciated, this is driving me to distraction.

Ragnar

 

[Sean Smart]

I wrote a sub that will process the data and tried to stick with how your
wrote the code originally. It probably isn't quite in the form that you're
looking for but feel free to take it apart :-D

NOTE: I've coded in the row (lnLastSourceRow ) and column (lnColumnCount )
variables, as wells as set the workbook and worksheet objects to the active
ones. You may wish to change this.

Option Explicit

Sub test()

Dim oCommonExcelWorkbook As Excel.Workbook
Dim oCurrentSheet As Excel.Worksheet

Dim lnRowIndex As Long
Dim lnLastSourceRow As Long
Dim lnSourceRow As Long
Dim lnColumnCount As Long

Dim strValue As String

'You'll probably want to delete these two variables:
lnLastSourceRow = 9
lnColumnCount = 15

'May not be required:
Set oCurrentSheet = ActiveSheet
Set oCommonExcelWorkbook = ActiveWorkbook

For lnRowIndex = lnLastSourceRow To 1 Step -1

Cells(lnRowIndex, 1).Select

strValue = oCurrentSheet.Cells(lnRowIndex, 1).Value, 1

' Will only process cells if "r" is present. Will ignore any other value.
Select Case strValue
Case "r"
oCurrentSheet.Activate

lnSourceRow = ActiveCell.Row

Range(Cells(lnSourceRow, 1), Cells(lnSourceRow,
lnColumnCount)).Select

Selection.Delete

End Select

' Save once it has deleted all the rows.
oCommonExcelWorkbook.Save

Next lnRowIndex

End Sub

 

[Ragnar Midtskogen]

Thank you Sean,

I started to modify my code to match yours and found a couple of mistakes, I
had forgotten to change lnSourceRow to lnRowIndex in the range select
statement.
When I changed that, my code works.

I had extensively revised my code and was going to make the changes but
forgot.

I had a feeling it was something like that, I didn't think Excel was that
weird.
I had tried changing the Delete to Clear and the behavior was pretty much
the same, so it looked like a bug, but I never saw it.

Ragnar