MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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.

Kevin


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:

ActiveCell.EntireRow.Delete

Regards,
BarrieBarrie Davidson

Posted by Kevin on September 21, 2001 6:07 AM

Barrie,

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.

Thanks,
Kevin

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?

BarrieBarrie Davidson

Posted by Tom Urtis on September 21, 2001 6:40 AM

Kevin,

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 _
Destination:=Sheet2.Range("A65536").End(xlUp).Offset(1, 0)

HTH

Tom Urtis

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.

Thanks,
Kevin

Posted by Barrie Davidson on September 21, 2001 7:15 AM

Kevin, how about code something like this?

Sub YourSub()
Dim dummyFile As String
Dim dataFile As String

dataFile = ActiveWorkbook.Name
'Your code to filter, select, and copy the data
Workbooks.Add
dummyFile = ActiveWorkbook.Name
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.EntireRow.Delete
ActiveSheet.UsedRange.Select
Selection.Copy
'Your code to select the destination to copy the data to
Workbooks(dummyFile).Close (False)
Workbooks(dataFile).Activate
'Your code to clear the filters

End Sub


BarrieBarrie Davidson

Posted by Kevin on September 21, 2001 8:31 AM

Barrie,

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?

Just wondering,
Kevin

Posted by Barrie Davidson on September 21, 2001 8:50 AM

Can you add this to your filter criteria?

BarrieBarrie Davidson

Posted by Kevin on September 24, 2001 10:06 AM

Barrie,

This is what my code looks like right now:

Range("A5:D328").Select
Selection.AutoFilter
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?

Thanks,
Kevin

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
End Sub

If you only want it for one particlar sheet, put this in the sheet's module :-

Private Sub Worksheet_Activate()
Range("A1") = ActiveSheet.Name
End Sub


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.

Selection.SpecialCells(xlCellTypeVisible).Select

You can then copy via

Selection.Copy

Hope this is what you need.

Regards,
BarrieBarrie Davidson