Delete Entire Row doesn't delete row, code continues to run, no errors
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
excel vba
asked Dec 28 '18 at 7:50
Gary ChristopherGary Christopher
114
add a comment |
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
excel vba
asked Dec 28 '18 at 7:50
Gary ChristopherGary Christopher
114
Can u spare a sample data? The looping is problematic in your code.
– MD AZAD HUSSAIN
Dec 28 '18 at 8:08
1
Do not delet ethe rows in a loop. It is slow :) See my answer Here
– Siddharth Rout
Dec 28 '18 at 8:48
@Siddharth Rout: Nice, I would have done the same thing but parts '/// other operations' of the code are missing.
– VBasic2008
Dec 28 '18 at 9:03
add a comment |
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
excel vba
asked Dec 28 '18 at 7:50
Gary ChristopherGary Christopher
114
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
excel vba
excel vba
asked Dec 28 '18 at 7:50
Gary ChristopherGary Christopher
114
asked Dec 28 '18 at 7:50
Gary ChristopherGary Christopher
114
asked Dec 28 '18 at 7:50
Gary ChristopherGary Christopher
114
asked Dec 28 '18 at 7:50
Gary ChristopherGary Christopher
114
asked Dec 28 '18 at 7:50
Gary ChristopherGary Christopher
114
114
Can u spare a sample data? The looping is problematic in your code.
– MD AZAD HUSSAIN
Dec 28 '18 at 8:08
1
Do not delet ethe rows in a loop. It is slow :) See my answer Here
– Siddharth Rout
Dec 28 '18 at 8:48
@Siddharth Rout: Nice, I would have done the same thing but parts '/// other operations' of the code are missing.
– VBasic2008
Dec 28 '18 at 9:03
add a comment |
Can u spare a sample data? The looping is problematic in your code.
– MD AZAD HUSSAIN
Dec 28 '18 at 8:08
1
Do not delet ethe rows in a loop. It is slow :) See my answer Here
– Siddharth Rout
Dec 28 '18 at 8:48
@Siddharth Rout: Nice, I would have done the same thing but parts '/// other operations' of the code are missing.
– VBasic2008
Dec 28 '18 at 9:03
Can u spare a sample data? The looping is problematic in your code.
– MD AZAD HUSSAIN
Dec 28 '18 at 8:08
Can u spare a sample data? The looping is problematic in your code.
– MD AZAD HUSSAIN
Dec 28 '18 at 8:08
1
1
Do not delet ethe rows in a loop. It is slow :) See my answer Here
– Siddharth Rout
Dec 28 '18 at 8:48
Do not delet ethe rows in a loop. It is slow :) See my answer Here
– Siddharth Rout
Dec 28 '18 at 8:48
@Siddharth Rout: Nice, I would have done the same thing but parts '/// other operations' of the code are missing.
– VBasic2008
Dec 28 '18 at 9:03
@Siddharth Rout: Nice, I would have done the same thing but parts '/// other operations' of the code are missing.
– VBasic2008
Dec 28 '18 at 9:03
add a comment |
1 Answer
1
active
oldest
votes
Optimization?
You should use:
ActiveCell.EntireRow.Delete
If you post the whole code, it could be optimized to run more smoothly.
answered Dec 28 '18 at 8:38
VBasic2008VBasic2008
1,9902213
Thank you VBasic2008. This worked.
– Gary Christopher
Dec 28 '18 at 18:34
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53955281%2fdelete-entire-row-doesnt-delete-row-code-continues-to-run-no-errors%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Optimization?
You should use:
ActiveCell.EntireRow.Delete
If you post the whole code, it could be optimized to run more smoothly.
answered Dec 28 '18 at 8:38
VBasic2008VBasic2008
1,9902213
Thank you VBasic2008. This worked.
– Gary Christopher
Dec 28 '18 at 18:34
add a comment |
Optimization?
You should use:
ActiveCell.EntireRow.Delete
If you post the whole code, it could be optimized to run more smoothly.
answered Dec 28 '18 at 8:38
VBasic2008VBasic2008
1,9902213
Thank you VBasic2008. This worked.
– Gary Christopher
Dec 28 '18 at 18:34
add a comment |
Optimization?
You should use:
ActiveCell.EntireRow.Delete
If you post the whole code, it could be optimized to run more smoothly.
answered Dec 28 '18 at 8:38
VBasic2008VBasic2008
1,9902213
Optimization?
You should use:
ActiveCell.EntireRow.Delete
If you post the whole code, it could be optimized to run more smoothly.
answered Dec 28 '18 at 8:38
VBasic2008VBasic2008
1,9902213
answered Dec 28 '18 at 8:38
VBasic2008VBasic2008
1,9902213
answered Dec 28 '18 at 8:38
VBasic2008VBasic2008
1,9902213
answered Dec 28 '18 at 8:38
VBasic2008VBasic2008
1,9902213
1,9902213
Thank you VBasic2008. This worked.
– Gary Christopher
Dec 28 '18 at 18:34
add a comment |
Thank you VBasic2008. This worked.
– Gary Christopher
Dec 28 '18 at 18:34
Thank you VBasic2008. This worked.
– Gary Christopher
Dec 28 '18 at 18:34
Thank you VBasic2008. This worked.
– Gary Christopher
Dec 28 '18 at 18:34
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53955281%2fdelete-entire-row-doesnt-delete-row-code-continues-to-run-no-errors%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Can u spare a sample data? The looping is problematic in your code.
– MD AZAD HUSSAIN
Dec 28 '18 at 8:08
1
Do not delet ethe rows in a loop. It is slow :) See my answer Here
– Siddharth Rout
Dec 28 '18 at 8:48
@Siddharth Rout: Nice, I would have done the same thing but parts '/// other operations' of the code are missing.
– VBasic2008
Dec 28 '18 at 9:03