Delete Macro

[triegaardtm]

HI


I need help creating a Macro to delete a row.

Private Sub btnRemoveCrate()

Dim CrateRef As String
CrateRef = cbCrateRef.Text

**the Macro must find the selected text in the first column of active sheet and the delete the whole row containing that text.

Rows(???Containing the CrateRef???).Select
Selection.Delete Shift: = xlUP

End Sub

 

[Firefly2012]

Hi

Try:

Private Sub btnRemoveCrate()

Dim rFound As Range

Set rFound = Activesheet.Column("A").Find(What:=cbCrateRef.Text,LookAt:=xlPart,MatchCase:=False)
If Not rFound Is Nothing Then rFound.EntireRow.Delete
End Sub

 

[triegaardtm]

Gives me a runtime 438 error on the "set rFound" line.

(Object doesn't support the property or method)

 

[Firefly2012]

Sorry, that should have read "Columns":

Set rFound = Activesheet.Columns("A").Find(What:=cbCrateRef.Text,LookAt:=xlPart,MatchCase:=False)

 

[triegaardtm]

thanx firefly2012, its working.

Can you possibly explain why rFound is nothing? and then why it deletes the column. have no idea how your code works.

 

[Firefly2012]

The Not preceding the "rFound is Nothing" reads in English "assuming rFound is not nothing {ie it is something as value was found}".

So thatr check is required to ensure that the sought value actually was found in the column.

 

[triegaardtm]

Sorry missed the double negative.
Thank you for the help