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):

- "(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.

Thanks in advance,

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?