468,301 Members | 1,488 Online

Visual Basic Editor Excel: using filters

I have an excel file with columns A, B, C, D and E that look like this:

A B C D E
-----------------------------------------------------
N 23,179 15,744 28,550 14,007
C 22,757 16,635 29,661 12,011
C 21,493 17,400 29,319 12,011
O 21,266 17,733 28,155 15,999

The columns B, C and D contain the coordinates of an organic atom with the name of the atom in column A. I would like to filter each of the columns B, C and D on an lower and upper bound. Therefore I created a criteria that would like something like this (if there are 3116 rows):

Expand|Select|Wrap|Line Numbers
1. "(B1:B3116>=4,115783)*(B1:B3116<=8,115784)*(C1:C3116>=6,290975)*(C1:C3116<=10,29097)*(D1:D3116>=35,17968)*(D1:D3116<=39,17968)"
I would like to use this criteria with the AdvancedFilter method and copy the resulting range to an other worksheet. I apply the AdvancedFilter method on the range collection from the atomsSheet:

atomsSheet.range("B1", "D3116")

I use the action xlFilterCopy and the CopyToRange is the range collection from another coordinatesSheet:

coordinatesSheet.range("B1", "D3116")

If I run my script then I get an "Invalid reference" error on the AdvancedFilter method.

I could use the AutoFilter method 3 times on the columns but then I have to insert a new row at the top of the atomsSheet. Finally I have to iterate over the selected cells that contain all the filtered data to add a row like "N 23,179 15,744 28,550" as a new item of a list box.

It sounds quite simple filter the rows in the atomsSheet by a lower and upper bound for the x, y and z coordinates and add the resulting rows as items to a list box. The last part is simple but I cannot seem to get the first part working. So does anyone have some ideas? Can I use the above criteria all at once with another filter method?

All help is appreciated and it's not for me but for a student project from my girlfriend.

Mark

PS: Is there also an easy way in Visual Basic Editor to get the row number of the last cell that still contains data?
Nov 20 '06 #1
1 3448
albertw
267 100+
I have an excel file with columns A, B, C, D and E that look like this:

A B C D E
-----------------------------------------------------
N 23,179 15,744 28,550 14,007
C 22,757 16,635 29,661 12,011
C 21,493 17,400 29,319 12,011
O 21,266 17,733 28,155 15,999

The columns B, C and D contain the coordinates of an organic atom with the name of the atom in column A. I would like to filter each of the columns B, C and D on an lower and upper bound. Therefore I created a criteria that would like something like this (if there are 3116 rows):

Expand|Select|Wrap|Line Numbers
1. "(B1:B3116>=4,115783)*(B1:B3116<=8,115784)*(C1:C3116>=6,290975)*(C1:C3116<=10,29097)*(D1:D3116>=35,17968)*(D1:D3116<=39,17968)"
I would like to use this criteria with the AdvancedFilter method and copy the resulting range to an other worksheet. I apply the AdvancedFilter method on the range collection from the atomsSheet:

atomsSheet.range("B1", "D3116")

I use the action xlFilterCopy and the CopyToRange is the range collection from another coordinatesSheet:

coordinatesSheet.range("B1", "D3116")

If I run my script then I get an "Invalid reference" error on the AdvancedFilter method.

I could use the AutoFilter method 3 times on the columns but then I have to insert a new row at the top of the atomsSheet. Finally I have to iterate over the selected cells that contain all the filtered data to add a row like "N 23,179 15,744 28,550" as a new item of a list box.

It sounds quite simple filter the rows in the atomsSheet by a lower and upper bound for the x, y and z coordinates and add the resulting rows as items to a list box. The last part is simple but I cannot seem to get the first part working. So does anyone have some ideas? Can I use the above criteria all at once with another filter method?

All help is appreciated and it's not for me but for a student project from my girlfriend.

Mark

PS: Is there also an easy way in Visual Basic Editor to get the row number of the last cell that still contains data?
hi

little short in time, but answer to last question...

do
if not range("a1").offset(i,0).value=vbnullstring then LastRow=i
i=i+1
loop until range("a1").offset(i,0).value=vbnullstring
Nov 20 '06 #2