VBA - Offset to delete rows above activecell

[JoeRooney]

Hi ,

I have the below code and it is working as expected except that it is also deleting the activecell row, I only want to delete the two rows above and leave the row of the activecell.

Can someone advise what I need to change in the offset so the activecell row is not deleted?

Sub Find_Ex1()


Range("A:A").Find(What:="Renewal / Collected").Select

ActiveCell.Offset(2, 2).Range("A1").Select

If IsNumeric(ActiveCell.Value) And ActiveCell.Value <> 0 Then

MsgBox "Is a number and is not equal to zero"

Else

Range(ActiveCell.Row & ":" & ActiveCell.Offset(-2, 0).Row).Select

Selection.Delete Shift:=xlUp

End If


End Sub

 

[JoeMo]

Change this:

Range(ActiveCell.Row & ":" & ActiveCell.Offset(-2, 0).Row).Select

to this:

ActiveCell.Offset(-2, 0).Resize(2).EntireRow.Delete

and remove the Selection.Delete Shift:= xlUp line

 

[Fluff]

Try

Else
   ActiveCell.Offset(-2).Resize(2).EntireRow.Delete
End If

 

[JoeRooney]

Perfect , thanks guys

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[JoeRooney]

Hi guys sorry have another query , I have a problem with the below piece of my code

If IsNumeric(ActiveCell.Value) And ActiveCell.Value <> "" Then

If there is a zero found here it is taking it as blank , I want to differentiate between a zero and blank . Could you advise what I should input instead of "" ?

This is one way of solving my problem

Another way is looking at a different cell on the row for Text , I was hoping I could just change IsNumeric to IsText but that is causing an error.

If you could help me with either option that would be great.

Thank you,

 

[Fluff]

If the active cell is 0 then it's not "" & it is numeric, so you get the msgbox, if the cell is blank then you wont get the msgbox.

 

[JoeRooney]

For some reason it is reading it as a blank and not 0

 

[Fluff]

If the cell has a 0 are you getting the message box?

 

[JoeRooney]

It is working as expected now, thanks for all your help today , appreciate it.