Automation Error When Trying to Delete Row

Stefferoo

New Member
Joined
Feb 24, 2014
Messages
5
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?

Code:
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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,166
Try something like this...

Code:
[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

New Member
Joined
Feb 24, 2014
Messages
5
Try something like this...

Code:
[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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,166
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

New Member
Joined
Feb 24, 2014
Messages
5
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:


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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,166
Try this. It makes it case insensitive and removes any extraneous space characters.

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

Stefferoo

New Member
Joined
Feb 24, 2014
Messages
5
Try this. It makes it case insensitive and removes any extraneous space characters.

Code:
[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.

Code:
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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,166
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

New Member
Joined
Feb 24, 2014
Messages
5
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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,166
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
 

Forum statistics

Threads
1,081,860
Messages
5,361,734
Members
400,652
Latest member
cortexnotion

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top