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
- "(B1:B3116>=4,115783)*(B1:B3116<=8,115784)*(C1:C3116>=6,290975)*(C1:C3116<=10,29097)*(D1:D3116>=35,17968)*(D1:D3116<=39,17968)"
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?