+ Reply to Thread
Results 1 to 9 of 9

Macro to Autofilter a table and delete filtered rows: Error 1004 cannot alter table

  1. #1
    Registered User
    Join Date
    07-04-2018
    Location
    England
    MS-Off Ver
    2016
    Posts
    4

    Macro to Autofilter a table and delete filtered rows: Error 1004 cannot alter table

    Hi All,

    I've created this little macro to Autofiler a table in a specified sheet and delete the filtered rows however whenever I run the code an error 1004 appears stating This cannot occur as it would alter cells within my table. I'm unsure as to why it wont allow me to alter rows in a table through code as I don't appear to have any restrictions placed on either the worksheet or the table.

    Could it be the code (incorrect delete statement?):

    Sheets("UK Feed").Select
    ActiveSheet.Range("$A$1:$H$3548").AutoFilter Field:=1, Criteria1:="No"
    Rows("2:2").Select
    Rows("2:3549").Select
    Selection.Delete Shift:=xlUp
    ActiveSheet.Range("$A$1:$H$2").AutoFilter Field:=1
    Range("A1").Select

    I believe the syntax is correct but i'm unsure - any help would be greatly appreciated!

    Kind regards,

    B

  2. #2
    Valued Forum Contributor PFDave's Avatar
    Join Date
    05-17-2012
    Location
    Milton Keynes, England
    MS-Off Ver
    Excel 2013
    Posts
    1,067

    Re: Macro to Autofilter a table and delete filtered rows: Error 1004 cannot alter table

    You're filtering but then you're asking to delete all rows between 2 and 3549 regardless of their filter result.

    Are you looking to delete all rows which contain "No" in column A?
    Please do add reputation where you see fit, it's nice to be nice and we all enjoy a pat on the back

    Please also mark your thread as solved once it has been.

  3. #3
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Macro to Autofilter a table and delete filtered rows: Error 1004 cannot alter table

    Is your sheet protected?

    Can you attach a sample workbook (not a picture or pasted copy)? Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  4. #4
    Registered User
    Join Date
    07-04-2018
    Location
    England
    MS-Off Ver
    2016
    Posts
    4
    Quote Originally Posted by PFDave View Post
    You're filtering but then you're asking to delete all rows between 2 and 3549 regardless of their filter result.

    Are you looking to delete all rows which contain "No" in column A?
    Hi,

    Yes - the idea was to filter column A to display only “No” then select all rows and delete as I believe this acts as deleting only the technically visible rows and therefore leaving the rows labelled “Yes”

  5. #5
    Valued Forum Contributor PFDave's Avatar
    Join Date
    05-17-2012
    Location
    Milton Keynes, England
    MS-Off Ver
    Excel 2013
    Posts
    1,067

    Re: Macro to Autofilter a table and delete filtered rows: Error 1004 cannot alter table

    Quote Originally Posted by brytsyt View Post
    Hi,

    Yes - the idea was to filter column A to display only “No” then select all rows and delete as I believe this acts as deleting only the technically visible rows and therefore leaving the rows labelled “Yes”
    Another way

    Please Login or Register  to view this content.
    No need to filter if coding in my opinion

  6. #6
    Registered User
    Join Date
    07-04-2018
    Location
    England
    MS-Off Ver
    2016
    Posts
    4

    Re: Macro to Autofilter a table and delete filtered rows: Error 1004 cannot alter table

    Should have test data attached now
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    07-04-2018
    Location
    England
    MS-Off Ver
    2016
    Posts
    4
    Quote Originally Posted by PFDave View Post
    Another way

    Please Login or Register  to view this content.
    No need to filter if coding in my opinion
    Using this macro produced the error “overflow” possible there’s too many records? I’m dealing with atleast 47k of rows in the table

  8. #8
    Valued Forum Contributor PFDave's Avatar
    Join Date
    05-17-2012
    Location
    Milton Keynes, England
    MS-Off Ver
    Excel 2013
    Posts
    1,067

    Re: Macro to Autofilter a table and delete filtered rows: Error 1004 cannot alter table

    Brytsyt; in your test sheet you don't have the value "No" in any column let alone A so this does really fit the purpose

    If you use my code and run the macro on the sheet you require (where "No" values are actually in column A it will work

    It works out how many rows you have to check

    Please Login or Register  to view this content.
    We can neaten up the code to refer to the sheet name etc after

  9. #9
    Valued Forum Contributor PFDave's Avatar
    Join Date
    05-17-2012
    Location
    Milton Keynes, England
    MS-Off Ver
    Excel 2013
    Posts
    1,067

    Re: Macro to Autofilter a table and delete filtered rows: Error 1004 cannot alter table

    Quote Originally Posted by brytsyt View Post
    Using this macro produced the error “overflow” possible there’s too many records? I’m dealing with atleast 47k of rows in the table
    Yes, try

    Please Login or Register  to view this content.
    instead of Integer

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Delete Table Rows of filtered data - when some columns are hidden
    By BG1983 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-01-2017, 10:42 PM
  2. [SOLVED] Autofilter and Delete Rows Excluded from the Filtered Selection
    By Aquamore in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-17-2015, 01:42 PM
  3. Alter table error - vba
    By mikey3580 in forum Access Programming / VBA / Macros
    Replies: 1
    Last Post: 02-28-2015, 04:55 PM
  4. [SOLVED] Can you delete filtered rows in a table?
    By Butcher1 in forum Excel General
    Replies: 2
    Last Post: 02-25-2015, 01:01 PM
  5. [SOLVED] Macro to copy filtered rows into new individual worksheets. Runtime Error 1004- line 25
    By J_Lee_M in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-23-2013, 01:27 AM
  6. Macro from filtered pivot table runtime error
    By dentdntn in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-28-2012, 11:25 AM
  7. [SOLVED] Macro To Delete Filtered Pivot Table Data
    By Roger in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-01-2006, 12:00 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1