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

chazrab

Well-known Member
Joined
Oct 21, 2006
Messages
877
Office Version
  1. 365
Platform
  1. Windows
RE: using Autofilter to finad all values < 0 in a column range...

Well, I thought I had this solved but it 's not working the way it should...
Code:
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
End Sub
BALANCES2018 is of course, a named range on Sheet REALBALS. There's no header row and the
range is only two columns A and B. The values on row 1 are
Code:
12/17 - 12/23	    $1,386.20
There is only ONE value <0 - on the last row which is row 54
and it is
Code:
12/23 - 12/29	    ($454.25)
When run, the code above is always giving this result copied to cells A1:B2 inthe REPORT sheet:
Code:
12/17 - 12/23    	$1,386.20 
12/23 - 12/29    	($454.25)
Why is it always grabbing that first value on row A1:B1 and including it in the copy code ?
(A1:B1 is always where the Autofilter drop down buttons are placed by this code)

The correct value copied over should just be
Code:
12/23 - 12/29    	($454.25)
and NOT include 12/17 - 12/23 $1,386.20, the first values in the range. Tried to make this as
ubnderstandable as possible. Sorry for the long explanantion.

Thanks for anyone's help.

cr
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Re: using Autofilter to finad all values < 0 in a column range...

Try changing

Code:
.SpecialCells(xlVisible).Copy Sheets("REPORT").Range("A1:B1")
to
Code:
.Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Copy Sheets("REPORT").Range("A1")
 
Upvote 0
Re: using Autofilter to finad all values < 0 in a column range...

Try changing

Code:
.SpecialCells(xlVisible).Copy Sheets("REPORT").Range("A1:B1")
to
Code:
.Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Copy Sheets("REPORT").Range("A1")

This offset.resize code DOES correctly copy only the last value as mentioned above as it should. But now there's another quirky problem - the copied result in cell A1 on the REPORT sheet is now this:
Rich (BB code):
12/23 - 12/29	#REF !
I did not think it relevant to mention that both column A and B values in BALANCES2018 are linked values on another sheet named BUDGET. As the values on the BUDGET sheet change, so will the corresponding links in the named range BALANCES2018. Any and all the values less than 0 are displayed in a userform listbox as the linked values in the BUDGET sheet change according to income and expenses. That way, I can "see ahead" in the future what happens when I manipulate amounts on the BUDGET sheet and can plan for the future. Maybe too much info, but why is it giving me this #REF !
but the correct single value, whereas my original code line gives the correct value displayed as links but always includes that top row:
Rich (BB code):
 12/17 - 12/23    	$1,386.20 
12/23 - 12/29    	($454.25)
as stated previously ? In order to see if the code is confusing Excel when copying linked values, I copied
column A1:B54 and pasted it onto itself as values. That's probably why the 12/23 - 12/29 appears as it should. Can't do that with column B though as it defeats the whole purpose explained from above. Sorry again for the dissertation - I just wanted to show what's going on as completely as possible.

Thanks for all your help.
cr
 
Upvote 0
Re: using Autofilter to finad all values < 0 in a column range...

Try putting

Code:
Sheets("REPORT").Range("A1")
back to
Code:
Sheets("REPORT").Range("A1:B1")

It shouldn't make any difference as you should only need to specify the top left cell in the paste/destination range.

I can't see any reason why an Offset/Resize would affect it unless the formula you have in B1 is reliant on the data that you are now not copying across.
 
Upvote 0
Re: using Autofilter to finad all values < 0 in a column range...

Try putting

Code:
Sheets("REPORT").Range("A1")
back to
Code:
Sheets("REPORT").Range("A1:B1")

It shouldn't make any difference as you should only need to specify the top left cell in the paste/destination range.

I can't see any reason why an Offset/Resize would affect it unless the formula you have in B1 is reliant on the data that you are now not copying across.

I think I solved the problem. See comments on this code:
Code:
Range("A1:B55").Select 'Range("BALANCES2018").Select  'range has header row 1
    Selection.AutoFilter 'starts autofilter on row 1 in header row
    With Sheets("REALBALS").Range("B1").CurrentRegion.Offset(1, 0) 'autofilter begins search 1 row below the header
      .AutoFilter Field:=2, Criteria1:="<0"
      .SpecialCells(xlVisible).Copy Sheets("REPORT").Range("A1")
    End With
what I see happening here is that it is necessary to use a header row to have Excel begin the autofilter search 1 row below (currentregion.offset(1,0) to the end of the range. If a header row is not used, Excel uses the first data row(row 1) to begin the search but includes that rows values in the search result regardless of whether row 1's values satisfy the criteria or not. That's about all I can figure out as to what's happening. It works now with a header row - for the time being.

Thanks again for all your help.
cr
 
Upvote 0
Re: using Autofilter to finad all values < 0 in a column range...

I am fully aware that Autofilter requires a header row. Surprised that you didn't realise it :eek:
 
Upvote 0

Forum statistics

Threads
1,213,585
Messages
6,114,520
Members
448,575
Latest member
hycrow

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top