Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Reference Issue
The filtered list is still showing up starting with cell B2. Any clues? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP reference issue | Excel Discussion (Misc queries) | |||
Cell Reference Issue | Excel Worksheet Functions | |||
Cell reference Issue | Excel Worksheet Functions | |||
Cell Reference Issue | Setting up and Configuration of Excel | |||
Reference Issue | Excel Programming |