Logo 
Search:

MS Office Forum

 Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Macro to delete entire row

  Asked By: Holly    Date: Dec 18    Category: MS Office    Views: 729
  

I got this piece of code that will delete an entire row when any cell
in column A is deleted.

How can I change it to target only Range("A10:A30")?

Any assistance will be dearly appreciated.


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 Then
If Target.Column = 1 And Target.Value = "" Then
Target.EntireRow.Delete Shift:=xlUp
End If
End If
End Sub

Share: 

 

2 Answers Found

 
Answer #1    Answered By: Nixie Schmidt     Answered On: Dec 18

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRange As Range
Set myRange = ActiveSheet.Range("A10:A30")
If Target.Count = 1 Then
If Not (Intersect(Target, myRange)) And Target.Value = "" Then
Target.EntireRow.Delete Shift:=xlUp
End If
End If
End Sub

 
Answer #2    Answered By: Isabelle Brown     Answered On: Dec 18

The only problem now was that when I entered data into A1 to A9 I got
the Runtime error "91": Object variable or With variable not set.

To overcome this, in the beginning I've added the line "On Error GoTo
HERE:" and just before "End Sub" I've added the line "HERE:".

Now it apparently looks fine.

 
Didn't find what you were looking for? Find more on Macro to delete entire row Or get search suggestion and latest updates.




Tagged:        

 
 
 
 

Related Post