Delete Entire Row doesn't delete row, code continues to run, no errors

Question

Using VBA in Excel to delete an entire row; code runs, row is not deleted, no errors are thrown.

My routine opens an existing file using Workbooks.Open filName, where filName is a string variable. File was originally saved with wbTemp.SaveAs fileName:=MyFileName, FileFormat:=xlOpenXMLWorkbook

The routine performs operations on specific cells, then calls to delete an entire row after the operations are completed. The line to be deleted, however, remains, the code continues to run, and no errors are thrown. I can (during debugging), manually delete the row in Excel. The data are in regular cells, not in table format. The worksheet is not protected, there is no password to open the file, and no other known anomalies that could explain why the delete row function isn't working.

This is a module in a workbook that displays planned and completed coursework and calculates GPA. Other modules create a specific set of coursework and import existing grades into the spreadsheet.

This module is used to update the spreadsheet each semester after grades are posted.

This specific line of code (in a different sub) works perfectly in the grade importing module, when the workbook is being created and grades imported.

Every line of code in this routine works in the file that is opened except the Rows(ActiveCell.Row).EntireRow.Delete line. These lines to delete the rows are the only source of trouble in the entire code.

The procedures exist in a macro-enabled template workbook (.xltm) and run on a file that is opened by the template (.xlsx). The commands to switch from one workbook to the other work exactly as they should.

I replaced Rows(ActiveCell.Row).EntireRow.Delete with Rows(80).EntireRow.Delete, with no change in behavior.

This one has me stumped. Any assistance would be greatly appreciated.

Private Sub cmdUpdateGrades_Click()

/// preliminary operations - all work correctly

lastrow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

j = 80

ActiveSheet.Range("A80").Activate

Do While j <= lastrow
    If ActiveCell.Value = "Course" Or ActiveCell.Value = "" Or _ 
ActiveCell.Value = "Term" Or ActiveCell.Value = "Cumulative" Then
        Rows(ActiveCell.Row).EntireRow.Delete
        j = j + 1
    ElseIf Left(ActiveCell.Value, 5) = "Term:" Then

/// other operations - work correctly

        Rows(ActiveCell.Row).EntireRow.Delete
        j = j + 1
    Else

/// other operations - work correctly

        ActiveSheet.Range("A80").Activate
        Rows(ActiveCell.Row).EntireRow.Delete
        j = j + 1
    End If
Loop

/// other operations 

End Sub

Can u spare a sample data? The looping is problematic in your code. — MD AZAD HUSSAIN
Do not delet ethe rows in a loop. It is slow :) See my answer Here — Siddharth Rout
@Siddharth Rout: Nice, I would have done the same thing but parts '/// other operations' of the code are missing. — VBasic2008

VBasic2008 VBasic2008 · Accepted Answer · 2018-12-28T08:38:06

Optimization?

You should use:

ActiveCell.EntireRow.Delete

If you post the whole code, it could be optimized to run more smoothly.

Delete Entire Row doesn't delete row, code continues to run, no errors

1 Answers

Optimization?