I want to select rows based on the values in three columns.

[chad portman]

I have a range of data that extends A1:AO120708 and I need to select every row based on three different criteria. The criteria is as follows: Col D needs to be <50, Col T needs to be >10, and Col V needs to state "Domestic". If all three of these data are met in any given row i need to select that row. Will also need to select Row 1 to grab the headers. I will do this a total of four times: <50,>10,"Domestic"; >50>10,"Domestic;<50,>10,"Import"; and the last time will be >50,>10,"Import". The code I currently use for the above which works is: (provided by TasosK from previous question)
VB:

Please Login or Register  to view this content.

However the problem I have now is that the data I am being given will not always be in the same columns could I make this work but instead of hard coding the columns have them as a varible based on certain headings?

Also posted at:
http://answers.microsoft.com/en-us/o...e-ce243b28a9ec
http://www.ozgrid.com/forum/showthre...060#post629060


Moderator Edit:

Welcome to the forum, chad portman.

Please notice that code tags have been added to your post. The forum rules require them so please keep that in mind and add them yourself whenever showing code in any of your future posts. To see instructions for applying them, click on the Forum Rules button at the top of the page and read Rule #3.
Thanks.

[MarkinTX]

I think you can solve your problem using the Advance filter.
This link contains a link to a sample with Advanced fiter
http://www.excelforum.com/excel-form...html?p=2939910

Once you have filtered your data, select all the rows and press Alt+; (alt and semi-colon) this will select only the visible cells and ignore the hidden ones.


For some more info on the Advanced filter I just finished a brief explination of it on my person blog.
http://excelevangelist.blogspot.com/

<-------Please click the star if this has helped you.

[FDibbins]

if the data you get is not always in the same column, the filter described by Markin may not find all of the instances you want. how/where are you getting your info from, and could it be modified to be more consitant?

[protonLeah]

To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post, you need to be using the main 'New Post' or 'New Thread' page and not 'Quick Reply'.
To use the main 'New Post' page, click the 'Post Reply' button in the relevant thread.


On this page, below the message box, you will find a button labelled 'Manage Attachments'.
Clicking this button will open a new window for uploading attachments.


You can upload an attachment either from your computer or from another URL by using the appropriate box on this page.
Alternatively you can click the Attachment Icon to open this page.


To upload a file from your computer, click the 'Browse' button and locate the file.


To upload a file from another URL, enter the full URL for the file in the second box on this page.
Once you have completed one of the boxes, click 'Upload'.


Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.
-----------------------------------------------------
If there are no column headers, explain you you know which columns to operate on.

[chad portman]

Ok so since the data I am working with is sensitive information I have made a dummy sample in an effort to get my goals across more clearly. So lets say I need to be able to copy all data that matches a crietia into a new worksheet. The crietia would be something like Import >=50 , Export >=50 , and Commision = "Y". With this set of sample data this could be done very simply with filter and copy paste but the true files I am working with have 500,000+ rows and 100+ columns.
In regards to Fdibbins question of where I get the data; it comes in from a varity of clients and while I might be able to rearrange things so that the columns were in the right spot I was really looking for a macro i can write so that when one of these files come in I just hit the button and get the data I need and nothign else.

[MarkinTX]

AF-8.jpg
The columns dont have to be in any order as long as the filter header and the table headers match it will find the right data.
See attached image that has only the 3 required fields in the filter data.

A macro can be written to set this up for you as well.

[MarkinTX]

In this example you can have a Filter Page
Import your table, and name it table.
Then run the macro.

Please Login or Register  to view this content.

Something like that would be a user friendly setup.

[chad portman]

Thank you guys for this thought however the problem is that while this works on a small scale liek the sample i sent when you apply it to a file like the ones i use it does not want to copy and paste due to the large number of sections.

Edit:
Added S/S of error

[MarkinTX]

What if you modifiy the filter code to the following

Please Login or Register  to view this content.

This autoselects only the visible cells, creates a new blank sheet and copies the data to it.
If you import a full data table, name it table and run it does this work?

[MarkinTX]

I think I have it.

Ok this method bypasses the need to select only visible and copy to another sheet.

Please Login or Register  to view this content.

<----Please click the star if this helps.

[MarkinTX]

The problem I was having is the Copy answers to another location has to be initiated from the tab you want to paste into.

This code makes a new page, then sets up the filter, and automatically copies the filtered data to the new page.

Works great on my end hope yours as well.

[chad portman]

MarkinTX you sir are a genius and if I could hug you I would.

[Cutter]

@ chad portman

Welcome to the forum.

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[MarkinTX]

Glad i could help.