VBA - Offset to delete rows above activecell

JoeRooney

Board Regular
Joined
Nov 27, 2017
Messages
116
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?

VBA Code:
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
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,324
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,130
Office Version
  1. 365
Platform
  1. Windows
Try
VBA Code:
Else
   ActiveCell.Offset(-2).Resize(2).EntireRow.Delete
End If
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,130
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Glad we could help & thanks for the feedback.
 

JoeRooney

Board Regular
Joined
Nov 27, 2017
Messages
116
Hi guys sorry have another query , I have a problem with the below piece of my code
VBA 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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,130
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,130
Office Version
  1. 365
Platform
  1. Windows
If the cell has a 0 are you getting the message box?
 

Watch MrExcel Video

Forum statistics

Threads
1,114,019
Messages
5,545,532
Members
410,690
Latest member
navneetr
Top