editing filter formulae

[patni]

Hey Guys,

I need help.

This question is however cross posted .
here is the link :


https://www.excelforum.com/excel-pr...-filter-modifying-formulae-2.html#post4942600

I have this code to filter sheet "PRODUCT" based on column "IN STOCK"
If cell matches "INSTOCK" then it is copied to sheet "stock in hand"

Now the problem is that the vba code clears the entire used range.
If it possible that to add an exception - to not clear upto row 6?

Because if i enter any data in row 1-6, then it autoclears it


Here is the vba code to edit

<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;">Private Sub Worksheet_Activate()
         Me.UsedRange.Clear
    With Worksheets("PRODUCT")
        .[Z1:Z2].Value = [{"stock in hand";"IN STOCK"}]
        .Cells(5, 1).ListObject.Range.AdvancedFilter xlFilterCopy, .[Z1:Z2], Cells(7,1)
        .[Z1:Z2].Clear
    End With
End Sub</code>

Please help me

 

[wideboydixon]

Try:

Me.UsedRange.Offset(6).Clear

WBD

 

[patni]

Try:

Me.UsedRange.Offset(6).Clear

WBD

Hey Thanks for your promt reply.

This code does not clear data upto row 6, however it does not copy the entire row from sheet"PRODUCT"

It copies only range A of sheet "product"
It fails to copy the data typed in other cell.

 

[patni]

The original code copies the entire row.
The code modified by you copies only 1st column.

 

[wideboydixon]

I didn't modify any code; I just pointed out how to retain rows 1:6.

WBD

 

[patni]

Hey ,

Sure. Thanks.

but when i emplement the changes recommended , it fails to copy the entire row.

Is there any way ?

 

[wideboydixon]

I'm not 100% familiar with AdvancedFilter but perhaps having data in row 6 will prevent it from working. How about:

Private Sub Worksheet_Activate()
    Dim savedRows
    savedRows = Range("1:6").Value
    Me.UsedRange.Clear
    With Worksheets("PRODUCT")
        .[Z1:Z2].Value = [{"stock in hand";"IN STOCK"}]
        .Cells(5, 1).ListObject.Range.AdvancedFilter xlFilterCopy, .[Z1:Z2], Cells(7, 1)
        .[Z1:Z2].Clear
    End With
    Range("1:6").Value = savedRows
End Sub

WBD

 

[patni]

Hey WBD,

THANK YOU SO MUCH.
It works wonder for me,


THANKKK YOU