code stops when cells are cleared

Microsoft Excel 2003 (full product)
February 3, 2010 at 06:20:02
Specs: Windows XP
Hi all,
The code below technically works fine, but the problem is, say for example in column 6 i gihtlight an entire row and delete it, i get a runtime error. Ive tried specifying that its oka if there is no text in the cell, but to no avail, hope you can help!

Private Sub Worksheet_Change(ByVal Target As Range)
If ActiveCell.Text = "" Then
If Target.Column = 6 And UCase(Target) = UCase(ActiveCell.Text) Then
If Target.Column = 6 And UCase(Target) <> UCase(ActiveCell.Text) Then
With Sheets(UCase(Target))
NxtRow = .Range("F" & Rows.Count).End(xlUp).Row + 1
Target.EntireRow.Copy
.Range("A" & NxtRow).PasteSpecial
Application.CutCopyMode = False
End With
End If
End If
End If
End Sub


See More: code stops when cells are cleared

Report •


#1
February 3, 2010 at 07:07:56
re: The code below technically works fine, but the problem is, say for example in column 6 i gihtlight an entire row and delete it, i get a runtime error.

Then the code doesn't "work fine".

re: "in column 6 i gihtlight an entire row and delete it"

How are you highlighting an entire row in a column?

Try this test:

Add this line as the first line of code:

MsgBox "The Target Address Is: " & Target.Address

Now delete a Row.

Note the Target.Address and think about what happens when the code tries to determine this value:

UCase(Target)


Report •

#2
February 3, 2010 at 08:07:25
If ActiveCell.Text = "" And Target.Column = 6 And UCase(Target) = UCase(ActiveCell.Text) And UCase(Target) <> UCase(ActiveCell.Text) Then
I'm surprised it does anything at all.

Report •

#3
February 3, 2010 at 10:17:48
@ Razor2.3:

I started down that path, but decided to leave it alone until I addressed the OP's original question as to why the code was throwing up an error.

If the cell in Column A of the row below the row that he deletes is empty, then ActiveCell.Text = "" will be TRUE when the Change code fires and the code will try to evaluate UCase(Target).

At that point the Target is an entire row and the code blows up.

So the code does do something - just not anything useful as far as I can tell.


Report •

Related Solutions


Ask Question