autofilter with a twist
Posted by Kevin on September 21, 2001 5:47 AM
I am using autofilter to take a list of accounts and their balances and filter out the accounts with zero balances. Is there any way using VB that I can have the program select the range of cells that have an account balance, and copy them to the clipboard? When I use autofilter, it automatically selects the range of filtered cells plus one row above the range of cells, and I cannot have this row above the filtered cells selected. Any help would be appreciated, thanks.
Posted by Barrie Davidson on September 21, 2001 5:56 AM
Kevin, I assume you mean the headings in your data table when you state "one row above the range of cells". You could copy all the data, including the headings, and then delete that first row after pasting the data (that's what I have done). Something like:
Posted by Kevin on September 21, 2001 6:07 AM
Yes that is exactly what I am talking about - but deleting the row after I paste is not really an option. I am pasting into a completely different accounting/billing program that our company uses, and if I paste the range in and it includes that top blank line, I get an error message and the program will not accept any of it. So I will need to figure out a way to remove that top line from the range before I copy the range to the clipboard.
Posted by Barrie Davidson on September 21, 2001 6:10 AM
Kevin, how about pasting the data to a blank worksheet, deleting the heading row, and then copying that data (from the blank worksheet) where you need it?
Posted by Tom Urtis on September 21, 2001 6:40 AM
Assuming the records being filtered are the ones with an accounts balance, see if this code does what you want. It assumes you are filtering on Sheet1, copying to Sheet2 and that you have already nailed the filter part of the code. It also allows for a dynamic range destination in case you are adding filtered records onto Sheet2 as you go:
Sheet1.UsedRange.Offset(1, 0).SpecialCells(xlTypeVisible).Copy _
Posted by Kevin on September 21, 2001 7:05 AM
That would work, but is there any way that this task can be easily automated, because otherwise we might as well just manually change the range selected before copying to the clipboard. My main concern is making this work with as few steps as possible, because the people using it are not very computer-literate.
Posted by Barrie Davidson on September 21, 2001 7:15 AM
Kevin, how about code something like this?
Dim dummyFile As String
Dim dataFile As String
dataFile = ActiveWorkbook.Name
'Your code to filter, select, and copy the data
dummyFile = ActiveWorkbook.Name
Application.CutCopyMode = False
'Your code to select the destination to copy the data to
'Your code to clear the filters
Posted by Kevin on September 21, 2001 8:31 AM
I will give this a try. Is there any way in VB to use an IF statement of some sort in order to select cells in a range based on certain conditions, for example after filtering, select only cells that have a value greater than zero?
Posted by Barrie Davidson on September 21, 2001 8:50 AM
Can you add this to your filter criteria?
Posted by Kevin on September 24, 2001 10:06 AM
This is what my code looks like right now:
Selection.AutoFilter Field:=4, Criteria1:=">0", Operator:=xlAnd
I am not really sure how I would have to modify this code to make it reselect the range of cells that contain only values greater than zero. Do you?
Posted by Barrie Davidson on September 25, 2001 5:27 AM
I think VBA is required. Here's one way. Put this in the workbook module if you want the sheet name in all sheets :-
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Range("A1") = ActiveSheet.Name
If you only want it for one particlar sheet, put this in the sheet's module :-
Private Sub Worksheet_Activate()
Range("A1") = ActiveSheet.Name
Posted by Barrie Davidson on September 25, 2001 5:30 AM
Don't know what's wrong with this board, but
Field:=4, Criteria1:=">0", Operator:=xlAnd
Mark, if you put this after your criteria code it will select the visible cells only.
You can then copy via
Hope this is what you need.