• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA Userform ActiveX 'Delete' Button is Not Working

I'm a beginner when it comes to VBA. I have a worksheet with 122 columns, so sending a sample is not possible. I'm hoping the error in the code will be easy for an expert to spot without the data.
To help manage this growing worksheet, I created a VBA Userform and so far it is fantastic!
My VBA userform includes a Delete Order Button. When clicked it performs two actions. Action1. It deletes the record selected in the worksheet (titled: "Master") and Action2. It clears the userform. Action2 is working fine (I only included 5 out of 122 lines of code here). Action1 is not working. Does anyone have insight on why?
Legend

Worksheet title: "Master"
First row of data: Row 3
Columns: A-DR

Action1
'Delete Order Button'
>>> use code - tags <<<
Code:
Private Sub DeleteOrder_Click()
Dim Shop_Order_Number As String
Shop_Order_Number = Trim(TextShopOrderNumber)
lastrow = Worksheets("Master").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastrow
If Worksheets("Master").Cells(i, 1).Value = Shop_Order_Number Then
Worksheets("Master").Rows(i).Delete
End If

'' Action2
Next 'The following code clears the textboxes and is working'
TextShopOrderNumber = ""
TextPrefix = ""
TextE10Status = ""
TextSuffix = ""
TextEmailSubjectLine = ""
End Sub
 
Last edited by a moderator:
Yodelayheewho
Have You verified that lastrow is something else than zero?
lastrow = Worksheets("Master").Cells(Rows.Count, 1).End(xlUp).Row
... Row.Count don't know which worksheet.

You could test something like below
Code:
With Worksheets("Master")
    lastrow = .Cells(.Rows.Count, 1).End(xlUp).Row
End With
 
Rows.Count does not need worksheet. It just supplies max available row in a given Excel version.

So .Cells(Rows.Count, 1).End(xlUp) will emulate CTRL + Up Arrow from last row in column A. Rows.Count = 1048576 if Excel 2007 or later.

Issue lies with this loop.
Code:
For i = 2 To lastrow
If Worksheets("Master").Cells(i, 1).Value = Shop_Order_Number Then
Worksheets("Master").Rows(i).Delete
End If
Next

When deleting, it must be looped from last row to first. Since deleting row will alter row index.

Code:
For i = lastrow to 2 Step -1
    If Worksheets("Master").Cells(i, 1).Value = Shop_Order_Number Then
        Worksheets("Master").Rows(i).Delete
    End If
Next

Edit: Added missing Next in code.
 
Last edited:
Yodelayheewho
Have You verified that lastrow is something else than zero?
lastrow = Worksheets("Master").Cells(Rows.Count, 1).End(xlUp).Row
... Row.Count don't know which worksheet.

You could test something like below
Code:
With Worksheets("Master")
    lastrow = .Cells(.Rows.Count, 1).End(xlUp).Row
End With

*Have you verified that lastrow is something else than zero?* Yes, I made sure all rows below the table are blank and the last row has data

I revised the code (see below) as I understood your suggestion, but it didn't work.

Code:
Private Sub DeleteOrder_Click() 'The first six lines of code delete the record in Master'
Dim Shop_Order_Number As String
Shop_Order_Number = Trim(TextShopOrderNumber)
With Worksheets("Master")
lastrow = .Cells(Rows.Count, 1).End(xlUp).Row
End With
For i = 2 To lastrow
If Worksheets("Master").Cells(i, 1).Value = Shop_Order_Number Then
Worksheets("Master").Rows(i).Delete
End If
Next 'The following code clears the textboxes'
 TextPrefix = ""
 TextE10Status = ""
 TextSuffix = ""
 TextShopOrderNumber = ""
 TextEmailSubjectLine = ""
 End Sub
 
Yodelayheewho
case lastrow
... what is its value with Your original code?
case my sample
... there are missing one dot before Rows
... and of course, it should do from last to 2 as #3 reply
 
Rows.Count does not need worksheet. It just supplies max available row in a given Excel version.

So .Cells(Rows.Count, 1).End(xlUp) will emulate CTRL + Up Arrow from last row in column A. Rows.Count = 1048576 if Excel 2007 or later.

Issue lies with this loop.
Code:
For i = 2 To lastrow
If Worksheets("Master").Cells(i, 1).Value = Shop_Order_Number Then
Worksheets("Master").Rows(i).Delete
End If
Next

When deleting, it must be looped from last row to first. Since deleting row will alter row index.

Code:
For i = lastrow to 2 Step -1
    If Worksheets("Master").Cells(i, 1).Value = Shop_Order_Number Then
        Worksheets("Master").Rows(i).Delete
    End If
Next

Edit: Added missing Next in code.

I'm not sure if I followed your instructions correctly, but the code didn't work...

Code:
'Delete Order Button'
Private Sub DeleteOrder_Click() 'The first six lines of code delete the record in Master'
Dim Shop_Order_Number As String
Shop_Order_Number = Trim(TextShopOrderNumber)
lastrow = Worksheets("Master").Cells(Rows.Count, 1).End(xlUp).Row
For i = lastrow To 2 Step -1
    If Worksheets("Master").Cells(i, 1).Value = Shop_Order_Number Then
        Worksheets("Master").Rows(i).Delete
    End If
Next
'The following code clears the textboxes'
 TextPrefix = ""
 TextE10Status = ""
 TextSuffix = ""
 TextShopOrderNumber = ""
 End Sub
 
Yodelayheewho
case lastrow
... what is its value with Your original code?
case my sample
... there are missing one dot before Rows
... and of course, it should do from last to 2 as #3 reply

case lastrow? What do you mean?
Here is my original code:

Code:
Private Sub DeleteOrder_Click() 'The first six lines of code delete the record in Master'
Dim Shop_Order_Number As String
Shop_Order_Number = Trim(TextShopOrderNumber)
lastrow = Worksheets("Master").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastrow
If Worksheets("Master").Cells(i, 1).Value = Shop_Order_Number Then
Worksheets("Master").Rows(i).Delete
End If
Next
'The following code clears the textboxes'
 TextPrefix = ""
 TextE10Status = ""
 TextSuffix = ""
 TextShopOrderNumber = ""
 TextEmailSubjectLine = ""
 End Sub
 
I'd recommend uploading sample workbook with full VBA project (desensitize it, while keeping data structure same).
As we can't confirm what's held in variables that are utilized in your code.

If code from #6 didn't work. It is likely one of the variables hold something unexpected and not finding matches.
 
case lastrow? What do you mean?
I mean that You should able to verify lastrow's value, that it is same as You can see number of rows in that sheet.

Below, You can see dot before Rows - every mark counts!
Code:
With Worksheets("Master")
    lastrow = .Cells(.Rows.Count, 1).End(xlUp).Row
End With

Instead of looping this-way-or-that-way those rows,
You could filter those rows with Shop_Order_Number (which You also should verify)
and after that delete all of those in one time.
You can get Your sample code by recording new Macro.
(( remember to have backups while testing ))
... as written in #8 reply - other could guess without a sample file.
Many times those match, but sometimes .... something else.
 
It is pretty hard to figure this out without a sample. So, please see attached. You'll see a text box with a list of things I want to accomplish. Please ignore. I'll work on each one at a time. This post is to get the 'Delete Order Button' working.
Thank you so much!!!!!
 

Attachments

  • Systems Order Entry Master_VBA_SAMPLE.xlsm
    87.5 KB · Views: 5
Yodelayheewho
Check this sample
... I couldn't test it normally ... because ActiveX-components.
but with my tests, it'll delete named order.

if TextShopOrderNumbers are unique
then You could use Match to find its row
instead of for ... next.
 

Attachments

  • Systems Order Entry Master_VBA_SAMPLE.xlsm
    81.1 KB · Views: 5
Last edited:
FANTASTIC! It works!
Shop Order Numbers are unique. Do you think that's important?
Gosh, I wish I understood why this worked. Thank you so much!!!
 
Yodelayheewho
As I wrote - if unique then You could use Match to find its row and delete it, instead of compare it row-by-row.
Why - keywords eg: Lastrow ... column, which You compare ... backward
 
Yodelayheewho
Code:
    On Error Resume Next
    With Worksheets("Master")
        i = WorksheetFunction.Match(Shop_Order_Number, .Range("D:D"), 0)
        If Err.Number = 0 Then
            .Rows(i).Delete
        Else
            Err.Clear
            MsgBox "Unknown Shop Order Number", vbInformation, Shop_Order_Number
            Exit Sub
        End If
    End With
 
Back
Top