Automation Error When Trying to Delete Row

[Stefferoo]

Hi,

I have been searching for a way to delete a row based on the value of a particular column. I keep finding "functional code" online, but they all result in automation errors for me.

Basically, what I want to accomplish is this: When the "delete activity" button is clicked, excel will search column B in the Activities Inventory for a cell that matches that value. If a match is found, that row will be deleted.

I have the following code written, but it is giving me an automation error. Can somebody please help me out with this?

Private Sub btnDeleteActivity_Click()
    Application.ScreenUpdating = False
    
    ActiveSheet.Select
    ActivityName = ActiveSheet.Name
    
    Application.DisplayAlerts = False
    
    Message = "Are you sure you want to delete the current Activity Worksheet named: " & ActivityName & "?"
    Style = vbYesNo + vbCritical + vbDefaultButton2
    
    response = MsgBox(Message, Style, "Delete Activity Worksheet")
    If response = vbNo Then Exit Sub
    
    ActiveSheet.Delete
    
    ActiveWorkbook.Sheets("Activities Inventory").Activate
    
    Dim LRow As Long, n As Long
     
    LRow = Range("B:B").End(xlUp).Row
        For n = LRow To 1 Step -1
        If Cells(n, 2).value = ActivityName Then Cells(n, 2).EntireRow.Delete
    Next n
    'ActiveWorkbook.Sheets("Menu").Select
    'MsgBox "Please select the refresh button to update the Concerns Log.", vbInformation, "Update Log"
End Sub

 

[AlphaFrog]

Try something like this...

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] btnDeleteActivity_Click()
    
    [color=darkblue]Dim[/color] ActivityName [color=darkblue]As[/color] [color=darkblue]String[/color], n [color=darkblue]As[/color] [color=darkblue]Long[/color]
    
    ActivityName = ActiveSheet.Name
    
    [color=darkblue]If[/color] MsgBox("Are you sure you want to delete the current Activity Worksheet named: " & ActivityName & "?", _
              vbYesNo + vbCritical + vbDefaultButton2, "Delete Activity Worksheet") = vbNo [color=darkblue]Then[/color] [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
    
    Application.ScreenUpdating = [color=darkblue]False[/color]
    Application.DisplayAlerts = [color=darkblue]False[/color]
    
    ActiveSheet.Delete
    Sheets("Activities Inventory").Select
    
    [color=darkblue]For[/color] n = Range("B" & Rows.Count).End(xlUp).Row [color=darkblue]To[/color] 1 [color=darkblue]Step[/color] -1
        [color=darkblue]If[/color] Cells(n, 2).Value = ActivityName [color=darkblue]Then[/color] Rows(n).Delete
    [color=darkblue]Next[/color] n
    
    Application.DisplayAlerts = [color=darkblue]True[/color]
    Application.ScreenUpdating = [color=darkblue]True[/color]
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[Stefferoo]

Try something like this...

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] btnDeleteActivity_Click()
    
    [COLOR=darkblue]Dim[/COLOR] ActivityName [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR], n [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    
    ActivityName = ActiveSheet.Name
    
    [COLOR=darkblue]If[/COLOR] MsgBox("Are you sure you want to delete the current Activity Worksheet named: " & ActivityName & "?", _
              vbYesNo + vbCritical + vbDefaultButton2, "Delete Activity Worksheet") = vbNo [COLOR=darkblue]Then[/COLOR] [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
    
    Application.ScreenUpdating = [COLOR=darkblue]False[/COLOR]
    Application.DisplayAlerts = [COLOR=darkblue]False[/COLOR]
    
    ActiveSheet.Delete
    Sheets("Activities Inventory").Select
    
    [COLOR=darkblue]For[/COLOR] n = Range("B" & Rows.Count).End(xlUp).Row [COLOR=darkblue]To[/COLOR] 1 [COLOR=darkblue]Step[/COLOR] -1
        [COLOR=darkblue]If[/COLOR] Cells(n, 2).Value = ActivityName [COLOR=darkblue]Then[/COLOR] Rows(n).Delete
    [COLOR=darkblue]Next[/COLOR] n
    
    Application.DisplayAlerts = [COLOR=darkblue]True[/COLOR]
    Application.ScreenUpdating = [COLOR=darkblue]True[/COLOR]
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Thanks for the reply, but I am still getting an automation error when I run that code.

 

[AlphaFrog]

Thanks for the reply, but I am still getting an automation error when I run that code.

What is the error description?

On the error dialog, click the Debug button. What line of code is highlighted?

Where did you place the code; what code module?

 

[Stefferoo]

What is the error description?

On the error dialog, click the Debug button. What line of code is highlighted?

Where did you place the code; what code module?

Your reply set off a lightbulb in my mind and got me past the automation error.

I realized I was deleting the worksheet the code was housed on before all of my procedures executed. (Hence the automation error.)

I tweaked the code, and now it deletes the sheet I want, but the row in the activities inventory still doesn't delete. Here is the new code:

Private Sub btnDeleteActivity_Click()
    Dim ActivityName As String, n As Long
    ActivityName = ActiveSheet.Name
    
    Sheets("Activities Inventory").Activate
    For n = Range("B" & Rows.Count).End(xlUp).Row To 1 Step -1
        If Cells(n, 2).value = ActivityName Then Rows(n).Delete
    Next n
    
    If MsgBox("Are you sure you want to delete the current Activity Worksheet named: " & ActivityName & "?", _
              vbYesNo + vbCritical + vbDefaultButton2, "Delete Activity Worksheet") = vbNo Then Exit Sub
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    Sheets(ActivityName).Select
    ActiveSheet.Delete
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

 

[AlphaFrog]

Try this. It makes it case insensitive and removes any extraneous space characters.

[color=darkblue]If[/color] LCase(Trim(Cells(n, 2).Value)) = LCase(Trim(ActivityName)) [color=darkblue]Then[/color] Rows(n).Delete

 

[Stefferoo]

Try this. It makes it case insensitive and removes any extraneous space characters.

[COLOR=darkblue]If[/COLOR] LCase(Trim(Cells(n, 2).Value)) = LCase(Trim(ActivityName)) [COLOR=darkblue]Then[/COLOR] Rows(n).Delete

That also didn't work.

I played around with the code some more and added message boxes to let me know which row it was on as it looped through the rows and how many rows it was detecting in the range.

Column B ends at row 18, but LR is equal to 12. Could it be because Column 12 is technically where the content begins? All cells rows 1 - 11 are empty because there are buttons and pictures in those rows. I just added text in all of the rows above 12, but it still runs into the same issue and says the range is only 12 rows.

LR = Range("B" & Rows.Count).End(xlUp).Row
    MsgBox (LR)
    For i = LR To 1 Step -1
        If Range("B" & i).value = ActivityName Then
            Rows(i).Delete
        Else
            MsgBox ("Value not found in row " & i)
        End If
    Next i

 

[AlphaFrog]

LR should be 18 if there is something in cell B18 regardless if the data starts in B12

You could change this to exclude rows 12 and above, but it wouldn't affect LR
For i = LR To 12 Step -1

 

[Stefferoo]

LR should be 18 if there is something in cell B18 regardless if the data starts in B12

You could change this to exclude rows 12 and above, but it wouldn't affect LR
For i = LR To 12 Step -1

Well, that makes no sense to me why it would be saying LR = 12 when there are more than 12 rows with text in them. I would post the spreadsheet, but it's highly confidential. Any more thoughts or am I at a loss for this particular feature?

 

[AlphaFrog]

I just noticed in post #7 you say the content starts in column 12. If yes try this...

LR = Range("L" & Rows.Count).End(xlUp).Row

Pictures and buttons don't count using the .End(xlUp) method