473,324 Members | 2,548 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

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.

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?
Nov 20 '06 #1
1 3590
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.

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?
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

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: JW | last post by:
I am looking for sample code to learn me about using excel from a visual basic program. I want to open a file, store code in a certain cell and save this again. Who can help me Regards
33
by: John Timbers | last post by:
I'd like to purchase Visual C# .Net for learning purposes only since it's a lot cheaper than Visual Studio (note that I'm a very experienced C++ developer). Can someone simply clarify the basic...
3
by: Omar | last post by:
Hi Developers, I am trying to access an Excel data file through a VB.Net application. I have the following code: =================================== VB.Net Code =================== Dim...
10
by: Dave Taylor | last post by:
I have a VB.NET WinForms project that has about 15 forms in it. When I opened the project yesterday, one of the form files does not open as a form (i.e. double-clicking it in Solution Explorer...
10
by: Steve | last post by:
I am trying to create a DLL in Visual Studio 2005-Visual Basic that contains custom functions. I believe I need to use COM interop to allow VBA code in Excel 2002 to access it. I've studied...
6
by: JimmyKoolPantz | last post by:
I have been given the task of converting a program from VFP (visual foxpro) to Visual Basic.net. My question is "Is it possible to generate a DBF file Dynamically(at runtime) using Visual...
3
by: marknoten | last post by:
I'm quite a newbie to Visual Basic and have a problem with the Input # statement in the Visual Basic Editor from Excel. I would like to read in a tab delimeted text file selected by a user with...
1
by: akshaysk | last post by:
HI, I am new to Visual Basic. For my Assignment i am supposed to write a visual basic program using excel. I have tried many scripts, but receive errors. Can anyone tell me of any website that...
1
by: chrspta | last post by:
I am new to Visual basic. I need a program using VB6 that converts txt files to excel file.Description is in the below: The form should have the Drive list, Dir list, file list and cmdConvert...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.