PDA

View Full Version : Solved: need to contain clearing of row within a range



ndendrinos
02-14-2011, 08:34 PM
Here is what I have:

Private Sub Worksheet_Change(ByVal Target As Range)
'this is to select adjacent cell after typing a quantity
If Not Intersect(Target, Range("A17:A35")) Is Nothing Then Target.Offset(0, 1).Select

'this in case user selects a duplicate item & gives the user the choice of adding same to the previous row or delete it
Dim rngFindRange As Range
Application.EnableEvents = False
If Intersect(Target, Range("A:B")) Is Nothing Or Me.UsedRange.Rows.Count = 1 Then GoTo TidyUp
If Target.Count > 1 Then
MsgBox "Please update only one cell at a time in columns A & B.", vbInformation
Application.Undo
GoTo TidyUp

End If
If Range("A" & Target.Row) <> "" And Range("B" & Target.Row) <> "" Then
Set rngFindRange = Range("B1:B" & Target.Row - 1).Find(What:=Range("B" & Target.Row), LookIn:=xlValues)
If Not rngFindRange Is Nothing Then

If MsgBox("Product exists on a previous row ... Add to it?", vbYesNo) = vbNo Then
Target.EntireRow.ClearContents
GoTo TidyUp
End If

rngFindRange.Offset(0, -1) = rngFindRange.Offset(0, -1) + Range("A" & Target.Row)

Target.EntireRow.ClearContents
End If
End If


Dim LastRow As Long
Range("A35").Select
LastRow = ActiveCell.End(xlUp).Offset(1, 0).Select
Selection.EntireRow.ClearContents
Application.EnableEvents = True

TidyUp:

End Sub
example:
I type qty 5 / & choose from the userform a product of my choice.
Assuming that I have already done this choice in a row above that one I get prompted to either add to the previous row or cancel.

In any decision the row I am typing in will be "cleared"

The problem is that in both instances the code clears the whole row instead of the row within columns [A:D] as it should.:D
The range where the edit takes place needs to be contained within [A17:E35]

P.S I am not responsible for the smiley that appears in my message.Underneath the first smiley there is the letter D

Simon Lloyd
02-14-2011, 09:08 PM
This is the line that is clearing the whole rowTarget.EntireRow.ClearContents
change it toRange("A" & Target.row & ":E" & Target.Row).ClearContentsYou also have Selection.EntireRow.ClearContents
If you still want to keep within your parameters change this too :)
(i am responsible for the smiley!, you can turn those off when clicking "Quote" and replying you get the option prior to submiting, i've disabled the smilies for you)

ndendrinos
02-14-2011, 09:19 PM
Works great now.
Thank you again Simon, It is 11:18PM over here and time for bed.
:hi:

Simon Lloyd
02-14-2011, 09:46 PM
lol it's 04:46am here!