RE: using Autofilter to find all values < 0 in a named column range.

[chazrab]

RE: using Autofilter to find all values < 0 in a named column range.

All I want to do is use Autofilter to find all values less than zero in a column range and just copy the result to the REPORT sheet. I have this so far in the click event in a button, and it works- to a point:

Private Sub cmdNegatives_Click()
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Sheets("REALBALS").Activate
    Range("BALANCES2018").Select  'new 12.17.17
    With Selection
    .AutoFilter Field:=2, Criteria1:="<0"
    .SpecialCells(xlVisible).Copy Sheets("REPORT").Range("A1:B1")
    End With
    Selection.AutoFilter
    Sheets("BUDGET").Activate
    Application.EnableEvents = True
    Application.ScreenUpdating = True
'' Selection.AutoFilter
End Sub

REALBALS is the sheet name that contains the named range "BALANCES2018". The "to a point" phrase means that when this code it run on the BALANCES2018 named range, it always includes the first value at the top of the named range along with the the rest of the correct negative values(<0) - why is the code including that first value at the top - I don't want that - I want the code to list only the negative values ("<0") in the range. I know it's got to be something simple.

Thanks for anyone's help.
cr

 

[Fluff]

Re: using Autofilter to finad all values < 0 in a named column range.

Does your named range contain a header row? If not the first row in the range will be used as a header. You can use

.offset(1).SpecialCells(xlVisible).Copy

but if there is no header & the first row contains a negative number that will not be copied over

 

[chazrab]

Re: using Autofilter to finad all values < 0 in a named column range.

Does your named range contain a header row? If not the first row in the range will be used as a header. You can use

.offset(1).SpecialCells(xlVisible).Copy

but if there is no header & the first row contains a negative number that will not be copied over

it doesn't have a header - but what you said clued me into what I failed to mention - that named range is in the MIDDLE of a column - it starts at row 57. The data above (rows 1 to 56) is old and from the past 11 months of this year. I deleted these rows which put the first row of the named range in row 1. After I inserted a new row in row 1 and gave A! and BI header names and ran it again, it worked - to say that it only started the autofilter sequence from row 2 - not row 1. Deleting the header row and having data values actually begin in row 1 still gave the correct result since there's nothing above row 1.

Thanks again - appreciate your help.
cr

 

[Fluff]

Re: using Autofilter to finad all values < 0 in a named column range.

Glad to help & thanks for the feedback