Results 1 to 15 of 15

Unable to Delete Table Rows

Threaded View

  1. #1
    Forum Contributor
    Join Date
    04-07-2015
    Location
    Halifax, NS
    MS-Off Ver
    365
    Posts
    100

    Unable to Delete Table Rows

    I am trying to add a Delete option on a userform. The record to delete is selected from a listbox and the userform is populated with the appropriate data.
    I have looked at the code step-by-step and it appears that the error occurs at the findvalue.EntireRow.Delete line. I temporarily added a textbox to the form to insure that the value for "findvalue" was correct and it was indicating that the proper record was being located in the underlying table however I get a runtime error. Any help or suggestions would be appreciated.


    Private Sub cmdDelete_Click()
    'declare the variables
    Dim findvalue As Range
    Dim cDelete As VbMsgBoxResult
    Dim cNum As Integer
    Dim DataSH As Worksheet
    Set DataSH = Sheet2
    Dim x As Integer
    'error statement
    'On Error GoTo errHandler:
    'hold in memory and stop screen flicker
    Application.ScreenUpdating = False
    'check for values
    If Bar1.Value = "" Or Bar2.Value = "" Then
    MsgBox "There is not data to delete"
    Exit Sub
    End If
    'give the user a chance to change their mind
    cDelete = MsgBox("Are you sure that you want to delete this product? The data cannot be retrieved!", _
    vbYesNo, "CAUTION!")
    If cDelete = vbNo Then
    MsgBox "Delete Cancelled", vbOKOnly, "Water & Bone"
    Exit Sub
    Else
    'find the row
    Set findvalue = DataSH.Range("BA:BA").Find(What:=Me.Bar2.Value, _
    LookIn:=xlValues, LookAt:=xlWhole)
    
    '*****************************************************************
    'This is a test box that I added to check the value of findvalue *
    'Everything works up to this point                               *
    'Me.txtTest.Value = findvalue
    '*****************************************************************
    
    'delete the entire row
    
    findvalue.EntireRow.Delete 'This is where I get the run time error 1004 delete method of range class failed
    End If
    
    On Error GoTo 0
    Exit Sub
    errHandler:
    'Protect all sheets if error occurs
    'Protect_All
    'show error information in a messagebox
    MsgBox "An Error has Occurred " & vbCrLf & "The error number is: " & _
    Err.Number & vbCrLf & Err.Description & vbCrLf & "Please notify Chef Jamie"
    
    End Sub
    Last edited by jeffreybrown; 12-15-2019 at 10:18 AM. Reason: Fixed code tags!
    Proud to be Canadian!

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Macro to Autofilter a table and delete filtered rows: Error 1004 cannot alter table
    By brytsyt in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-05-2018, 04:57 AM
  2. [SOLVED] Delete table rows in multiple table is specific range
    By 3345james in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-26-2017, 08:32 AM
  3. Unable to delete certain rows
    By Wiking in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-15-2016, 09:37 AM
  4. Unable to insert or delete rows
    By AvrilA in forum Excel General
    Replies: 3
    Last Post: 04-12-2016, 02:53 AM
  5. Unable to delete rows
    By Kingsta in forum Excel General
    Replies: 1
    Last Post: 06-01-2014, 05:49 AM
  6. [SOLVED] Unable to delete rows
    By Mrowe in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-19-2012, 03:08 PM
  7. Unable to delete rows or columns? Why?
    By Barney Quinn in forum Excel General
    Replies: 3
    Last Post: 09-17-2006, 09:43 PM

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