asked on VBA to delete rows in filtered table
Hi Experts!
Attached is some code I'm using to Filter one column in a Table and then delete the visible rows. I had difficulty with Selection.EntireRow.Delete
My problem is that the code stops and asks if I want to "Delete Entire Sheet Row?". I click "Yes" and it's fine. How can I have the code eliminate that step as well so it runs from start to finish without stopping?
This is very likely a no brainer for all of you. My attempt at code is below. Thank you!
Sub Filter_Sales1()
'
Dim RngToDelete As Range
Range("TblFTE").Select
ActiveSheet.ListObjects("T
Array("Business Development", "G&A", "Marketing", "R&D", "Solutions", "Support"), _
Operator:=xlFilterValues
Set RngToDelete = Selection.SpecialCells(xlC
RngToDelete.Delete
Range("TblFTE[[#Headers],[
ActiveSheet.ShowAllData
End Sub
Attached is some code I'm using to Filter one column in a Table and then delete the visible rows. I had difficulty with Selection.EntireRow.Delete
My problem is that the code stops and asks if I want to "Delete Entire Sheet Row?". I click "Yes" and it's fine. How can I have the code eliminate that step as well so it runs from start to finish without stopping?
This is very likely a no brainer for all of you. My attempt at code is below. Thank you!
Sub Filter_Sales1()
'
Dim RngToDelete As Range
Range("TblFTE").Select
ActiveSheet.ListObjects("T
Array("Business Development", "G&A", "Marketing", "R&D", "Solutions", "Support"), _
Operator:=xlFilterValues
Set RngToDelete = Selection.SpecialCells(xlC
RngToDelete.Delete
Range("TblFTE[[#Headers],[
ActiveSheet.ShowAllData
End Sub
Microsoft ExcelVB Script
Last Comment
xllvr
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Hang on a second. Just tried your suggestion with Application.DisplayAlerts=
I'm not sure what you mean by "hung up" and I don't see a line 11.
Yes, displayalerts would also work, but it shouldn't be needed if you delete using the entirerow property.
Dave
Yes, displayalerts would also work, but it shouldn't be needed if you delete using the entirerow property.
Dave
ASKER
Apologies...Line 10. The code halted at that point. I originally used EntireRow and had problems with it. I have read quite a few posts that showed the very same problem with using EntireRow in the code so I replaced it with what others used successfully. Using RngToDelete.Delete worked fine except I didn't want to have to stop and click "Yes" to Delete Entire Sheet Row to continue the code.
Using RngToDelete.Delete with the Display Alert message seems to be working. Thanks for sending that along as a potential solution!
Using RngToDelete.Delete with the Display Alert message seems to be working. Thanks for sending that along as a potential solution!
Perhaps it was choking on EntireRow because it was a table? Yes, any time there is an Excel Prompt you want to circumvent (almost anytime, there are a few exceptions), you can override and take the default "YES" with Application.DisplayAlerts = False.
Dave
Dave
ASKER
That's the only conclusion I could come up with as well. Grateful for the prompt tip, Dave!
-Nancy
-Nancy
Thanks for posting. The code hung up on line 11 when I changed line 12. Thoughts?