VBA error when trying to delete visible rows

KDavidP1987

Board Regular
Joined
Mar 6, 2018
Messages
51
Greetings all,

I am trying to run a script which is supposed to delete the visible rows from a table I've filtered. However, when the script is ran it comes up with error msg: :eek: "Runtime error '1004': Delete method of range class failed. :eek:

I have tried it two different ways, based on solutions I've found through MrExcel, StackOverflow, and Ozgrid.

Code:
Sub RemoveVisibleRows()
'Removes Visible rows, discluding the header

'Method 1
    Worksheets("IncidentData").Range("A1:A100000").Offset(1,0).SpecialCells(xlCellTypeVisible).EntireRow.Delete

'Method 2: (currently ' out)
    'Range("A2:A100000").SpecialCells(xlCellTypeVisible).EntireRow.Delete
    
End Sub
I've tried some other, lengthier, solutions as well to no avail. If you have any ideas I'm all ears, because I cannot figure it out. :confused:

If needed, here are some of the solution pages I had used:
https://www.mrexcel.com/forum/excel...w-delete-autofiltered-rows-except-header.html

https://www.ozgrid.com/forum/forum/...sible-rows-after-auto-filter-exclude-headings

Sincerely,
Kristopher
 
Last edited:

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Is the sheet protected?
 
Upvote 0
Missed this
supposed to delete the visible rows from a table
Try
Code:
Application.DisplayAlerts = False
ActiveSheet.ListObjects("Table1").DataBodyRange.SpecialCells(xlCellTypeVisible).Delete
Application.DisplayAlerts = True
 
Upvote 0
Is it important/desirable to delete the entire worksheet row rather than just the rows from the table?
This should just delete rows from the table area, not the whole sheet.
I have also made the assumption that after deletion you would want to remove any filters from the table to show any remaining data.
Code:
Sub DelTblRows()
  Dim rng As Range
  
  With ActiveSheet.ListObjects("Table1")
    Set rng = .DataBodyRange.SpecialCells(xlVisible)
    If .AutoFilter.FilterMode Then .AutoFilter.ShowAllData
    rng.Delete
  End With
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,309
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top