Auto Filter but exclude lines or data that includes a certain word

[oblix]

Hi there all
I have table that I allow users to click on icon to run vba to filter non blank rows before printing:

Sub Prep_Received()
    With ThisWorkbook.Worksheets("Received")
        Range("$B$2:$F$3000").AutoFilter Field:=1, Criteria1:="<>"
    End With
End Sub

I woud like to exclude lines 3 to 547 as I have hiddden it from user. Alternatively to exclude lines which has contains word :"Opening Stock".

The same goes for unfiltering. With unfilter it should not unfilter lines 3 to 547 and should remain hidden. the current code is:

Sub Clear_Prep_Received()
    With ThisWorkbook.Worksheets("Received")
        If .AutoFilterMode Then .AutoFilter.ShowAllData
    End With
End Sub

Any help would be appreciated and I will learn from your suggestions

 

[Anthony47]

Try extending the filter criterias as follow:

    ActiveSheet.Range("$B$2:$F$3000").AutoFilter Field:=1, Criteria1:="<>", _
        Operator:=xlAnd, Criteria2:="<>*Opening stock*"

For clearing the filter, indeed apply a filter with the following criteria:

    ActiveSheet.Range("$B$2:$F$3000").AutoFilter Field:=1, Criteria1:="<>*Opening stock*"

Try...

 

[Alex Blakenburg]

I woud like to exclude lines 3 to 547 as I have hiddden it from user

If you have used Rows > Hide to hide the rows, unfiltering or even removing the AutoFilter won't make them visible.
Only Rows > Unhide or changing the Row Height will make them visible.

 

[oblix]

Thank you for quick reply
It worked ...... sort of
I changed my code to:
Filter:

Sub Prep_Received()
    With ThisWorkbook.Worksheets("Received")
        Range("$B$2:$F$4905").AutoFilter Field:=4, Criteria1:="<>", _
        Operator:=xlAnd, Criteria2:="<>*OPENING STOCK*"
      End With
End Sub

and unfilter/clear filter to:

Sub Clear_Prep_Received()
       ActiveSheet.Range("$B$2:$F$4905").AutoFilter Field:=4, Criteria1:="<>*OPENING STOCK*"
End Sub

The clear filter / unfilter works fine but when I filter it starts at line963 where it was supposed to start at line 548.....
so it hides more than it should.

 

[oblix]

Okay Solved the problem.... but
Now when I send information from another sheet...... it overides the first line info (line3) instead of starting in line 485 (only does this when line 3 to 485 is hidden/filtered out
the code to send from sheet "PO" to sheet"Received" is:

Sub Send_to_Received()
Call TurnStuffOff
    Call Clear_PO_Filter
    Call Adv_Filter_PO
   
Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim lCopyLastRow As Long
Dim lDestLastRow As Long

'Set variables for copy and destination sheets
Set wsCopy = Worksheets("PO")
Set wsDest = Worksheets("Received")

'2. Find first blank row in the destination range based on data in column B
'Offset property moves down 1 row
lDestLastRow = wsDest.Cells(wsDest.Rows.Count, 1).End(xlUp).Offset(1).Row

'1. Find last used row in the copy range based on data in column A
lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "AS").End(xlUp).Row

'3. Copy & Paste Data
wsCopy.UsedRange.Copy
wsCopy.Range("AS6:AY" & lCopyLastRow).Copy

'wsDest.Range ("B" & lDestLastRow)
wsDest.Range("B" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

'Select the destination sheet
wsDest.Activate
Range("A1").Select
Call TurnStuffOn
End Sub

 

[Anthony47]

If you think this is due to filtered rows, then remove all filters at the beginning and apply them just before the end of the macro

Remember that paste always works on contiguous cells, hidden or visible doesn't matter.

 

[oblix]

Cleared contents in blanks cells...working now
thanks for inputs

 

[Anthony47]

Thank you for the feedback