VBA: Search for value and select and delete row with that value

[Kristina96]

Hello everyone,

I want to rearrange some data in an excel table.
I have a variable ID_Nr defined as double and it is a number between 2 and 100. I want the macro to look for that number in column A and if found select the whole row and delete it.
That way I am trying to eliminate all rows with a value that already appeared. It is supposed to keep only the first row with the entry "ID_Nr." in column A.

My tries lead to errors:

Cells(1, 1).Select
    Cells.Find(What:="ID_Nr.", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
    ActiveCells.Rows.Select
Selection.Delete

Thank you very much in advance for any kind of help!
Kristina

 

[CountTepes]

Try this

ActiveCell.EntireRow.Delete

 

[Kristina96]

I tried but it is not selecting the cell with the value. I get the error for the "cells.find..." line.

 

[CountTepes]

Try it this way

Dim C as Range

Cells(1, 1).Select
Set C=Cells.Find(What:="ID_Nr.", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
C.EntireRow.Delete

 

[Kristina96]

it still gives an error (91) for the line with the "find..." What could be the problem here?

 

[CountTepes]

If it finds nothing then it will set C to nothing.

Get rid of the .activate which is what is causing the issue.

However the C.entirerow.delete, will also fail. so you need to add a test for C being nothing.

If not C is Nothing then C.EntireRow.Delete.

 

[Fluff]

If ID_Nr is a variable then it should not be in quotes, also it should not have a period (.) after it.