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
– MD AZAD HUSSAIN, Commented Dec 28, 2018 at 8:08
Do not delet ethe rows in a loop. It is slow :) See my answer Here — Siddharth Rout
– Siddharth Rout, Commented Dec 28, 2018 at 8:48
@Siddharth Rout: Nice, I would have done the same thing but parts '/// other operations' of the code are missing. — VBasic2008
– VBasic2008, Commented Dec 28, 2018 at 9:03

VBasic2008 · Accepted Answer · 2018-12-28 08:38:06Z

0

Optimization?

You should use:

ActiveCell.EntireRow.Delete

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

answered Dec 28, 2018 at 8:38

VBasic2008

56.9k5 gold badges21 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Optimization?

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Optimization?

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related