Deleting entire rows

Question

My question is, why whenever I try to debug the error is

Runtime error 91; Object variable or with block variable not set.

I try to look at another example and try to find the solution in the forum but still, I cannot find it.

Btw, when I debug it will highlight at

findvalue.EntireRow.Delete

May I know what is the error ya? Hopefully, there is someone can explain it to me.

Thank you.

Private Sub cmdDelete_Click()

Dim findvalue As Range
Dim cDelete As VbMsgBoxResult
Dim cNum As Integer
Dim DataSH As Worksheet
Set DataSH = Sheet1
Dim x As Integer


Application.ScreenUpdating = False

If Emp1.Value = "" Or Emp2.Value = "" Then
MsgBox "There is not data to delete"
Exit Sub
End If

cDelete = MsgBox("Are you sure that you want to delete this training", _
vbYesNo + vbDefaultButton2, "Are you sure????")
If cDelete = vbYes Then

Set findvalue = DataSH.Range("B:B").Find(What:=Me.Emp1.Value, _
LookIn:=xlValues, LookAt:=xlWhole)

findvalue.EntireRow.Delete
End If

cNum = 7
For x = 1 To cNum
Me.Controls("Emp" & x).Value = ""
Next

DataSH.Range("A2").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("Data!$L$8:$L$9"), CopyToRange:=Range("Data!$N$8:$T$8"), _
Unique:=False

If DataSH.Range("N9").Value = "" Then
lstEmployee.RowSource = ""
Else

lstEmployee.RowSource = DataSH.Range("outdata").Address(external:=True)
End If

DataSH.Select
With DataSH
.Range("A2:G10000").Sort Key1:=Range("E2"), Order1:=xlAscending, Header:=xlGuess
End With

Sheet1.Select

On Error GoTo 0
Exit Sub

End Sub

score 0 · Accepted Answer · 2018-10-09T06:43:54.797

0

You simply aren't finding the value from Me.Emp1.Value in column B of the Data worksheet so there is Nothing to delete. Maybe expand the Find arguments; matchcase:=false comes to mind.

Error controlled:

Set findvalue = DataSH.Range("B:B").Find(What:=Me.Emp1.Value, _
LookIn:=xlValues, LookAt:=xlWhole)

if not findvalue is nothing then findvalue.EntireRow.Delete

Alternate:

dim m as string
m = application.match(Me.Emp1.Value, DataSH.Range("B:B"), 0)
if not iserror(m) then DataSH.rows(m).entirerow.delete

Additional AdvancedFilter range definitions:

with DataSH
    .Range("A2").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
            CriteriaRange:=.Range("L8:L9"), CopyToRange:=.Range("N8:T8"), _
            Unique:=False
end with

edited Oct 09 '18 at 06:43

answered Oct 09 '18 at 06:07

Thanks, it's actually in column A. – Oct 09 '18 at 06:14
1

btw, if that code is not in a public module and not in the Data worksheet's private code sheet (and it seems highly likely that it is not) then you will have trouble with the AdvancedFilter's CriteriaRange and CopyToRange range references. – Oct 09 '18 at 06:32
Omg, yes T.T I am having it right now. It said -- method range of object _global failed. Can you explain to me why this happen? – Oct 09 '18 at 06:40
Because on a worksheet's private codesheet, unqualified Range belong to that worksheet. You are trying to say *reference Data!L8:L9 on the Sheet2 worksheet*. See [Is the . in .Range necessary when defined by .Cells?](https://stackoverflow.com/questions/36368220/is-the-in-range-necessary-when-defined-by-cells) for a thorough explanation. – Oct 09 '18 at 06:46
tldr; using Range("Data!$N$8:$T$8") style is unreliable, it may work, it may not. So don't ever use it. – Oct 09 '18 at 06:47
Oh, that means like if my sheet is Master Data so I need to change Data to Master Data? Is that what you mean? – Oct 09 '18 at 06:50
No. If you are on the 'Master Data' private code sheet, you cannot use Range("Data!L8:L9") because Range belongs to 'Master Data', not Data. – Oct 09 '18 at 06:52
Its okay, ill try to read and understand the link that you already gave. Thanks ya :) – Oct 09 '18 at 06:57

Deleting entire rows

1 Answers1