Code Reference Issue
 

In need of help!

Does anyone know how I could change the code below to reference to B13
in stead of B2..?
It filters info in column A to Column B which works fine but I need it
to start the filtered list at B13.

(I've tried everything!)



Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRow As Long
If Target.Cells.Count 1 Then Exit Sub
If Target.Column < 1 Then Exit Sub


Application.EnableEvents = False
If Application.CountIf(Range("B:B"), Target.Value) = 0 Then
Range("B65536").End(xlUp)(2).Value = Target.Value
End If


For myRow = Range("B65536").End(xlUp).Row To 2 Step -1
If IsError(Application.Match(Cells(myRow, 2).Value, Range("A:A"),
False)) Then
Cells(myRow, 2).ClearContents
End If

Next myRow


Application.EnableEvents = True

End Sub

Code Reference Issue
 

For myRow = Range("B65536").End(xlUp).Row To 2 Step -1

to

For myRow = Range("B65536").End(xlUp).Row To 13 Step -1

--
Regards,
Tom Ogilvy



"J.W. Aldridge" wrote:

In need of help!

Does anyone know how I could change the code below to reference to B13
in stead of B2..?
It filters info in column A to Column B which works fine but I need it
to start the filtered list at B13.

(I've tried everything!)



Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRow As Long
If Target.Cells.Count 1 Then Exit Sub
If Target.Column < 1 Then Exit Sub


Application.EnableEvents = False
If Application.CountIf(Range("B:B"), Target.Value) = 0 Then
Range("B65536").End(xlUp)(2).Value = Target.Value
End If


For myRow = Range("B65536").End(xlUp).Row To 2 Step -1
If IsError(Application.Match(Cells(myRow, 2).Value, Range("A:A"),
False)) Then
Cells(myRow, 2).ClearContents
End If

Next myRow


Application.EnableEvents = True

End Sub

Code Reference Issue
 

I don't see any code here referencing a filter. Your filter may have been
manually setup through the Excel menu items, Data-Filter.

"J.W. Aldridge" wrote in message
oups.com...
In need of help!

Does anyone know how I could change the code below to reference to B13
in stead of B2..?
It filters info in column A to Column B which works fine but I need it
to start the filtered list at B13.

(I've tried everything!)



Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRow As Long
If Target.Cells.Count 1 Then Exit Sub
If Target.Column < 1 Then Exit Sub


Application.EnableEvents = False
If Application.CountIf(Range("B:B"), Target.Value) = 0 Then
Range("B65536").End(xlUp)(2).Value = Target.Value
End If


For myRow = Range("B65536").End(xlUp).Row To 2 Step -1
If IsError(Application.Match(Cells(myRow, 2).Value, Range("A:A"),
False)) Then
Cells(myRow, 2).ClearContents
End If

Next myRow


Application.EnableEvents = True

End Sub

Code Reference Issue
 

Oops! I missed that. See Tom's respons.
Thanks.

"PCLIVE" wrote in message
...
I don't see any code here referencing a filter. Your filter may have been
manually setup through the Excel menu items, Data-Filter.

"J.W. Aldridge" wrote in message
oups.com...
In need of help!

Does anyone know how I could change the code below to reference to B13
in stead of B2..?
It filters info in column A to Column B which works fine but I need it
to start the filtered list at B13.

(I've tried everything!)



Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRow As Long
If Target.Cells.Count 1 Then Exit Sub
If Target.Column < 1 Then Exit Sub


Application.EnableEvents = False
If Application.CountIf(Range("B:B"), Target.Value) = 0 Then
Range("B65536").End(xlUp)(2).Value = Target.Value
End If


For myRow = Range("B65536").End(xlUp).Row To 2 Step -1
If IsError(Application.Match(Cells(myRow, 2).Value, Range("A:A"),
False)) Then
Cells(myRow, 2).ClearContents
End If

Next myRow


Application.EnableEvents = True

End Sub

Code Reference Issue
 

The filtered list is still showing up starting with cell B2.

Any clues?

Code Reference Issue
 

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRow As Long
If Target.Cells.Count 1 Then Exit Sub
If Target.Column < 1 Then Exit Sub


Application.EnableEvents = False
If Application.CountIf(Range("B:B"), Target.Value) = 0 Then
rw = Range("B65536").End(xlUp)(2).Row
if rw < 13 then rw = 13
Cells(rw,2).Value = Target.Value
End If


For myRow = Range("B65536").End(xlUp).Row To 13 Step -1
If IsError(Application.Match(Cells(myRow, 2).Value, Range("A:A"),
False)) Then
Cells(myRow, 2).ClearContents
End If

Next myRow


Application.EnableEvents = True

End Sub

--
Regards,
Tom Ogilvy

"J.W. Aldridge" wrote:


The filtered list is still showing up starting with cell B2.

Any clues?