Move line as Worksheet change not working correctly

  • Thread starter Thread starter Risky Dave
  • Start date Start date
R

Risky Dave

Hi,

I have the following code that I found at
www.eggheadcafe.com/community/aspnet/66/10084345/cut-row.aspx

Private Sub Worksheet_Change(ByVal Target As Range)
If Left(Target.AddressLocal(ColumnAbsolute:=False), 1) = "M" Then
InsPos = Sheets("Archive").Range("a65536").End(xlUp).Row + 1
Rows(Target.Row).Copy Sheets("Archive").Rows(InsPos)
Rows(Target.Row).Delete shift:=xlUp
End If
End Sub

This works when I enter a value in column "M", but if I chnage the reference
to column AH it does not work (nothing happens at all, no errors, no
cut-and-past).

CAn anyone explain why this is happening (so that I understand) and et me
know how to modify the code so that the cut-and-paste happens when a value is
entered in column AH?

TIA

Dave
 
This is surely because in the Left() function you're looking at the first
character only, which can never be equal to "AH".
Why not use:
If Target.column = 13 ... [or for AH, 34]
 
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 34 Then Exit Sub 'col AH is col 34
With Sheets("archive")
lr = .Cells(Rows.Count, 1).End(xlUp).Row + 1
Rows(Target.Row).Cut .Rows(lr)
Rows(Target.Row).Delete
End With
End Sub
 
aardvark & Don

My thanks - fixed

Don Guillett said:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 34 Then Exit Sub 'col AH is col 34
With Sheets("archive")
lr = .Cells(Rows.Count, 1).End(xlUp).Row + 1
Rows(Target.Row).Cut .Rows(lr)
Rows(Target.Row).Delete
End With
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Click to expand...
 
Back
Top