How to select a subset of a table with Excel VBA in the manner of SELECT -> FROM -> WHERE -


Sorry this is a classic newbie question, but I can not find any concrete answer.

I'm completely new to Excel VBA and for programming in general. What I am trying to do is to select a subset of data from a large Excel sheet that is almost in this format: 

 name data a data two data N --------- --- ---------------------------------------- The person is a xxx person xxx person One xxx person will be a new sheet to dry for every few days, but I do not know how many people will be listed on it or how many entries are there. Each person. 
 
 My last goal (for now ...) is to take all the data for the person and copy a new sheet (in the same workbook) to a person, copy all the data For the person two, for a new sheet named person two, and so on. 
 
 If my basic understanding of SQL is correct then I would be able to use the command in the form. Data from the selection table where person = person one 
 
 However, I'm not looking for an easy way to do this in Excel. I can see that there are ways in which I can do this by using a loop for the cycle through the line and note the point where the 'person one' to 'person two' changes and then those categories Define. 
> I also see that I can use the AutoFilter function to select that data - which I want to do - I can filter the first column for a unique record on the second sheet, then the AutoFilter Circle through each person's names in settings. 
 
 It has also happened to me that I can use a loop to run through entries in the first column and under each I can insert a blank row, so select the table function Each person will select each part of the data. 
 
 Is there a better way to accomplish my end? I would have thought that this scenario is very common, but neither is it or I am searching on the wrong things. 
 
 Cheers! 
 
 Stephen

You can use ADO:

< pre> object dim RS as object dim Rs as dim CRS string as string dim strSQL as dim Strcon strFile = ActiveWorkbook.FullName '' Note HDR = No, then F1, F2 Etc. are used for column names if "HDR = yes, the name" in the first line of the range can be used. StrCon = "Provider = Mi Crosoft.jet.OLEDB.4.0; data source = "& amp; Strong_ and amp; "; Extended Properties =" "Excel 8.0; HDR = Yes; IMX =" 1 ";" Set ( "ADODB.Connection") to rs = CreateObject ( "ADODB.Recordset") cn.Open strCon 'Case Sensitive strSQL = "SELECT [Name], [Data One], [Data Two], [Data N] From [Sheet 1 $] "_ & amp; "WHERE [name] = 'person1'" rsssl, cn, 3, 3 worksheets ("sheet2"). Room (2, 1) .CopyFromRecordset RS

Note that you can select a different [name] and cycle through the resulting record set to receive a letter for each person .


Comments

Post a Comment

Popular posts from this blog

sql - dynamically varied number of conditions in the 'where' statement using LINQ -

I am using my first project LINQ (in mvc), so maybe there's something very simple that I remember. However, a day of searching and experimenting does not work, so it works. I am trying to write a LINQ query (Linux to SQL) in which in many cases where the statement is different from an OR or an AND. We do not know how many conditions are going in the query till runtime. This search is for filter control, where the user can choose multiple criteria for filtering. Select from table where table.col = 1 or table.col = 2 or table.col = 7 .... 'Other number of conditions Before I just create the SQL query as a string, while looping over all the situations. However, it seems that there should be a good way to do this in LINQ. I tried to use Expression trees, but they had a second thought for this moment on my head, where the lambda function was to be executed inside the statement such as: values ​​for matchingRows = matchingRows for each value Where (function (row) row.c...
Read more

asp.net mvc - Dynamically Generated Ajax.BeginForm -

I have a problem with ASP.NET MVC Ajax forms. I generate many Ajax form in a table such as: & Lt; Input type = "submit" name = "activity" value = "add activity" / & gt; & Lt;%}% & gt; & Lt; / TD & gt; & Lt; / TR & gt; & Lt;%}% & gt; So far so good. When I post one of those forms, it gives a partial view in a div, and in this new partial view a new Ajax Beginnform (which is not in Genesis form) This second Ajax Beginnorf is exactly like the ones I got on the table, but when I post it, I do not get the "Ajax Post", but a normal post that sends me to a new page, I do not know whether the two Ajax forms What is the reason for the difference between, why is it the "dynamically generated" Ajax form behaves like a normal HTML form? By the way, I know that I can do it in different ways using javascript, but I would like to understand whether it can only be done using MVC helplars and Ajax lib...
Read more

Debug on symbian -

I am using trk for phone debug This works well for the Holorld project But when I start the project in phone debug mode 1) the load failed 2) TrkProtocolPlugin: to target the specified file Failure (please verify If any body understands what problem I am experiencing, then this problem helps me Pre-release If the application has the privileges assigned (with the appropriate certificate) itemprop = "text" > In your case, I will check: / li> If there is no conflict of application ID with any other application of device If there are problematic commands in the installation package (for example copy copy for non-accessible directories)
Read more