Delete a row based on criteria

M

MoOsEBoT88

New Member
Joined
Sep 13, 2011
Messages
15
Office Version
  1. 365
Platform
  1. Windows
I want to be able to delete a row based on Cell B3, B5 selects which worksheet to delete the row off.

It also deletes the row of a central search sheet.

Here's the code I currently have but I get an 424: Object Required Error

Code: 
Sub DeleteRecord()
Application.ScreenUpdating = True
Application.Run "UnlockSheet"

Dim I As Integer

    On Error GoTo validateError

          If Range("E4") = "NOREC" Then Range("B3").Select: Err.Raise 513


If MsgBox("Do you want to delete this record", vbQuestion + vbYesNo) = vbYes Then

Dim strName As String
strName = Range("B5")
Sheets(strName).Activate

Application.Run "UnlockSheet"

'Deletes Row in Worksheet
  Dim LastRow As Long
  Dim rng As Range
     
      
    With ActiveWorksheet
        Columns("A:B").ColumnWidth = 9.71
        ActiveWorksheet.Rows(1).Insert
        ActiveWorksheet.Range("A1").Value = "Temp"
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        Set rng = .Range("A1").Resize(LastRow)
        rng.AutoFilter Field:=1, Criteria1:="=" & Worksheets("DeleteEdit").Range("B3").Value
        On Error Resume Next
        Set rng = rng.SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
        If Not rng Is Nothing Then rng.EntireRow.Delete
    End With

'Resize column back to 0
Columns("A:B").ColumnWidth = 0
     
'Deletes from search
Dim LastRow2 As Long
Dim rng2 As Range
     
     
    With Worksheets("Search")
         
        .Rows(1).Insert
        .Range("A1").Value = "Temp"
        LastRow2 = .Cells(.Rows.Count, "A").End(xlUp).Row
        Set rng2 = .Range("A1").Resize(LastRow2)
        rng2.AutoFilter Field:=1, Criteria1:="=" & Worksheets("DeleteEdit").Range("B3").Value
        On Error Resume Next
        Set rng2 = rng2.SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
        If Not rng2 Is Nothing Then rng2.EntireRow.Delete
    End With
    
'Clear B3 Contents
Application.Run "ClearDelete"

MsgBox "Record Deleted", VbMsgBoxStye = vbOKOnly, Completed
Application.Run "DeleteAnother"

Else
MsgBox "Operation Cancelled", VbMsgBoxStye = vbOKOnly, Cancelled
End If

Application.ScreenUpdating = True
Application.Run "LockSheet"
Exit Sub
I'm not fluent in VB, just copied and pasted from previous code snippets.

Hope you can spot my errors.
 
Last edited:

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Sort by date Sort by votes
Pressing F8 in the workbook it stops at

Code: 
...
Sheets(strName).Activate

Application.Run "UnlockSheet"

'Deletes Row in Worksheet
  Dim LastRow As Long
  Dim rng As Range
     
      
    With ActiveWorksheet <-- THIS LINE HERE
        Columns("A:B").ColumnWidth = 9.71
        ActiveWorksheet.Rows(1).Insert
        ActiveWorksheet.Range("A1").Value = "Temp"

...
Then jumps down the error defination at the bottom of the code.
 
Upvote 0
Doesn't look like you need that With/End With code

Remove those lines
Code: 
Application.Run "UnlockSheet"  
'Deletes Row in Worksheet   
Dim LastRow As Long   
Dim rng As Range              
Columns("A:B").ColumnWidth = 9.71 
ActiveWorksheet.Rows(1).Insert 
ActiveWorksheet.Range("A1").Value = "Temp" 
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row 
Set rng = .Range("A1").Resize(LastRow) 
rng.AutoFilter Field:=1, Criteria1:="=" & Worksheets("DeleteEdit").Range("B3").Value 
On Error Resume Next 
Set rng = rng.SpecialCells(xlCellTypeVisible) 
On Error GoTo 0 
If Not rng Is Nothing Then rng.EntireRow.Delete
 
Upvote 0
Took the With ActiveWorksheet and End With out of the code and it comes up with

Invalid or Unqualified reference highlighting this row

Code: 
Sub DeleteRecord()
Application.ScreenUpdating = True
Application.Run "UnlockSheet"

Dim I As Integer

    On Error GoTo validateError

          If Range("E4") = "NOREC" Then Range("B3").Select: Err.Raise 513


If MsgBox("Do you want to delete this record", vbQuestion + vbYesNo) = vbYes Then

Dim strName As String
strName = Range("B5")
Sheets(strName).Activate

Application.Run "UnlockSheet"

'Deletes Row in Worksheet
  Dim LastRow As Long
  Dim rng As Range

Columns("A:B").ColumnWidth = 9.71
.Rows(1).Insert                                    [COLOR=Red]<<<< THIS ROW HERE[/COLOR]
.Range("A1").Value = "Temp"
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set rng = .Range("A1").Resize(LastRow)
rng.AutoFilter Field:=1, Criteria1:="=" & Worksheets("DeleteEdit").Range("B3").Value
On Error Resume Next
Set rng = rng.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Delete


'Resize column back to 0
Columns("A:B").ColumnWidth = 0
     
'Deletes from search
Dim LastRow2 As Long
Dim rng2 As Range
     
     
    With Worksheets("Search")
         
        .Rows(1).Insert
        .Range("A1").Value = "Temp"
        LastRow2 = .Cells(.Rows.Count, "A").End(xlUp).Row
        Set rng2 = .Range("A1").Resize(LastRow2)
        rng2.AutoFilter Field:=1, Criteria1:="=" & Worksheets("DeleteEdit").Range("B3").Value
        On Error Resume Next
        Set rng2 = rng2.SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
        If Not rng2 Is Nothing Then rng2.EntireRow.Delete
    End With
    
    
'Clear B3 Contents
Application.Run "ClearDelete"

MsgBox "Record Deleted", VbMsgBoxStye = vbOKOnly, Completed
Application.Run "DeleteAnother"

Else
MsgBox "Operation Cancelled", VbMsgBoxStye = vbOKOnly, Cancelled
End If

Application.ScreenUpdating = True
Application.Run "LockSheet"
Exit Sub

validateError:
Dim errText As String
'   Check what the problem is.
    Select Case Err.Number
        Case 513
            errText = "No Ref No"
        Case Else
            errText = Err.Number & ": " & Err.Description
    End Select
'   Display error message and exit.
    MsgBox errText, vbOKOnly, "Error"
    Exit Sub
End Sub
 
Upvote 0
Looks like you did more than that.

This is the code I am proposing
Code: 
Sub DeleteRecord() 
Application.ScreenUpdating = True 
Application.Run "UnlockSheet"  
Dim I As Integer      
On Error GoTo validateError            
If Range("E4") = "NOREC" Then Range("B3").Select: Err.Raise 513   
If MsgBox("Do you want to delete this record", vbQuestion + vbYesNo) = vbYes Then  
Dim strName As String 
strName = Range("B5") Sheets(strName).Activate  
Application.Run "UnlockSheet"  
'Deletes Row in Worksheet   
Dim LastRow As Long   
Dim rng As Range                       
Columns("A:B").ColumnWidth = 9.71         
ActiveWorksheet.Rows(1).Insert         
ActiveWorksheet.Range("A1").Value = "Temp"         
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row         
Set rng = .Range("A1").Resize(LastRow)         
rng.AutoFilter Field:=1, Criteria1:="=" & Worksheets("DeleteEdit").Range("B3").Value         
On Error Resume Next         
Set rng = rng.SpecialCells(xlCellTypeVisible)         
On Error GoTo 0         
If Not rng Is Nothing Then rng.EntireRow.Delete   
'Resize column back to 0 Columns("A:B").ColumnWidth = 0       
'Deletes from search Dim LastRow2 As Long Dim rng2 As Range                 With Worksheets("Search")                   
.Rows(1).Insert         
.Range("A1").Value = "Temp"         
LastRow2 = .Cells(.Rows.Count, "A").End(xlUp).Row         
Set rng2 = .Range("A1").Resize(LastRow2)         
rng2.AutoFilter Field:=1, Criteria1:="=" & Worksheets("DeleteEdit").Range("B3").Value         
On Error Resume Next         
Set rng2 = rng2.SpecialCells(xlCellTypeVisible)         
On Error GoTo 0         
If Not rng2 Is Nothing Then rng2.EntireRow.Delete     
End With      
'Clear B3 Contents Application.Run "ClearDelete"  
MsgBox("Record Deleted", VbMsgBoxStye = vbOKOnly, Completed) Application.Run "DeleteAnother"  
Else MsgBox("Operation Cancelled", VbMsgBoxStye = vbOKOnly, Cancelled) End If  
Application.ScreenUpdating = True 
Application.Run "LockSheet" 
Exit Sub
 
Upvote 0
Code: 
Sub DeleteRecord()  
Application.ScreenUpdating = True  
Application.Run "UnlockSheet"   
Dim I As Integer       
On Error GoTo validateError 
            If Range("E4") = "NOREC" Then Range("B3").Select: Err.Raise 513 

If MsgBox("Do you want to delete this record", vbQuestion + vbYesNo) = vbYes Then   

Dim strName As String  
strName = Range("B5") 
Sheets(strName).Activate   
Application.Run "UnlockSheet"   
'Deletes Row in Worksheet    
Dim LastRow As Long    
Dim rng As Range                        
Columns("A:B").ColumnWidth = 9.71          
Worksheet(strName).Rows(1).Insert          
Worksheet(strName).Range("A1").Value = "Temp"          
LastRow = Worksheet(strName).Cells(.Rows.Count, "A").End(xlUp).Row 
         Set rng = Worksheet(strName).Range("A1").Resize(LastRow)          
rng.AutoFilter Field:=1, Criteria1:="=" & Worksheets("DeleteEdit").Range("B3").Value          

On Error Resume Next          
Set rng = rng.SpecialCells(xlCellTypeVisible)          
On Error GoTo 0          
If Not rng Is Nothing Then rng.EntireRow.Delete    
'Resize column back to 0 
Columns("A:B").ColumnWidth = 0 
       'Deletes from search 
Dim LastRow2 As Long 
Dim rng2 As Range                 
With Worksheets("Search")                    
.Rows(1).Insert          
.Range("A1").Value = "Temp"          
LastRow2 = .Cells(.Rows.Count, "A").End(xlUp).Row          
Set rng2 = .Range("A1").Resize(LastRow2)          rng2.AutoFilter Field:=1, Criteria1:="=" & Worksheets("DeleteEdit").Range("B3").Value 

On Error Resume Next 
         Set rng2 = rng2.SpecialCells(xlCellTypeVisible)          
On Error GoTo 0          
If Not rng2 Is Nothing Then rng2.EntireRow.Delete      
End With       
'Clear B3 Contents 
Application.Run "ClearDelete"   MsgBox("Record Deleted", VbMsgBoxStye = vbOKOnly, Completed) 
Application.Run "DeleteAnother"   
Else 
MsgBox("Operation Cancelled", VbMsgBoxStye = vbOKOnly, Cancelled) 
End If   

Application.ScreenUpdating = True  
Application.Run "LockSheet"  
Exit Sub
 
Upvote 0
Got it, thanks very much for the help!

Code: 
Sub DeleteRecord()
Application.ScreenUpdating = True
Application.Run "UnlockSheet"
Dim I As Integer
On Error GoTo validateError
            If Range("E4") = "NOREC" Then Range("B3").Select: Err.Raise 513

If MsgBox("Do you want to delete this record", vbQuestion + vbYesNo) = vbYes Then

Dim strName As String
strName = Range("B5")
Sheets(strName).Activate
Application.Run "UnlockSheet"
'Deletes Row in Worksheet
Dim LastRow As Long
Dim rng As Range
Columns("A:B").ColumnWidth = 9.71
Worksheets(strName).Rows(1).Insert
Worksheets(strName).Range("A1").Value = "Temp"
LastRow = Worksheets(strName).Cells(Rows.Count, "A").End(xlUp).Row
         Set rng = Worksheets(strName).Range("A1").Resize(LastRow)
rng.AutoFilter Field:=1, Criteria1:="=" & Worksheets("DeleteEdit").Range("B3").Value

On Error Resume Next
Set rng = rng.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Delete
'Resize column back to 0
Columns("A:B").ColumnWidth = 0
       'Deletes from search
Dim LastRow2 As Long
Dim rng2 As Range
With Worksheets("Search")
.Rows(1).Insert
.Range("A1").Value = "Temp"
LastRow2 = .Cells(.Rows.Count, "A").End(xlUp).Row
Set rng2 = .Range("A1").Resize(LastRow2)
rng2.AutoFilter Field:=1, Criteria1:="=" & Worksheets("DeleteEdit").Range("B3").Value

On Error Resume Next
         Set rng2 = rng2.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng2 Is Nothing Then rng2.EntireRow.Delete
End With
'Clear B3 Contents
Application.Run "ClearDelete"
MsgBox "Record Deleted"
Application.Run "DeleteAnother"
Else
MsgBox "Operation Cancelled"
End If

Application.ScreenUpdating = True
Application.Run "LockSheet"
Exit Sub

validateError:
Dim errText As String
'   Check what the problem is.
    Select Case Err.Number
        Case 513
            errText = "No Record to Delete"
        End Select
'   Display error message and exit.
    MsgBox errText, vbOKOnly, "Error"
Exit Sub
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question Question
Combining two codes by Using a Messagebox like Form with yes/no or alike
Replies
1
Views
370
lojanica
L
E
  • Solved
ActiveCell vs Active range search
Replies
3
Views
443
Joe4
Joe4
S
  • Solved
Delete rows that contain highlighting
Replies
5
Views
106
Serafin54
S
N
  • Question Question
Finding the differences then copy and paste code to a new sheet
Replies
1
Views
663
myall_blues
myall_blues
M
  • Solved
Deleting rows from a Range variable
Replies
4
Views
330
Umma123
U

Forum statistics

Threads
1,224,586
Messages
6,179,711
Members
452,939
Latest member
WCrawford

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top