Select range of data based on values in colum A

**eddiej90** · 01-06-2016, 08:33 AM

I have several large sets of data in one spreadsheet and would like to split them into different sheets. For each set of data, the values within column A are identical and amount to approximately 40,000 rows for each data set.

I have found a macro which compares the active cell to the next cell and moves down the rows until the numbers change. At this point it should stop, but it doesn't. The macro is:

Please Login or Register  to view this content.

Even if this worked, I am sure it would take an age to go through all 40,000 rows for the data set. And a lifetime for all 2,000 data sets that it needs to go through.

Can anyone recommend a quick way of exporting each data set into a different sheet? If not please fix the macro above.

Many thanks

**JapanDave** · 01-06-2016, 08:56 AM

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

**joe31623** · 01-06-2016, 09:22 AM

I modified this code per the attached spreadsheet and will upload a file that contains the macro w/ a button

Please let me know if this worked for you and if not why. If it did not work, please upload a sample sheet of data (just ew of your thousands of rows).

If anyone else came up with a solution that is better, please feel free to offer any criticism of my code that will help me help others more effectively. I know that it's best to avoid Copy and Paste methods whenever possible; however, this isn't a macro that requires a great deal of speed since it will be performed once.

'/Assumptions:
'/Each data point is contained w/i one row (true based on spreadsheet)
'/Data begins at row 2 with row 1 being the title or row (see StartRow, which may be changed as-needed) -- true based on spreadsheet
'/Requires the macro be created on the worksheet that contains the data (may be modified from this req. easily)

Please Login or Register  to view this content.

Last Updated Code: 10:55 AM (Eastern Std. Time on 1/6/2016) but the 10:46AM update should work -- all I did was change some formatting, remove an unnecessary variable, change the Double datatype to Long datatype (which shouldn't really matter either way b/w Double & Long), and clean up some formatting.

**eddiej90** · 01-06-2016, 09:35 AM

Hi Joe,

My aim is in fact to actually copy and paste each data set into a separate worksheet, which will run a model and export results. Then move onto the next data set. I just said split into different sheets for ease of explanation. I can edit it to export to the correct worksheet but please find attached a sample of the data to my original post.

Many thanks

**joe31623** · 01-06-2016, 09:40 AM

Eddie,

Do you mean separate workbook or worksheet? Either solution can easily be accomplished using my code above. I'll take a look at the sample and get back to you with any questions.

Joe

**eddiej90** · 01-06-2016, 09:55 AM

Sorry, meant workbook

**joe31623** · 01-06-2016, 10:38 AM

Eddie,

I modified the code above to work. Not sure why but I'm having issues uploading the spreadsheet so let me know if you can copy and paste the above code and get it to work.

Should take me 1/2 hr or less to create another macro to create workbooks for each sheet. I'm on it.

Joe

**eddiej90** · 01-06-2016, 10:57 AM

Hi Joe,

The macro you've created is great, however it does stop with a debug error after splitting two data sets at this point: DataSetRowEnd = r - 1

I'll just explain my task to you quickly.

I have a huge set of data that needs running through an excel model, once for each meter and then the results are exported to a separate workbook.

So, I have three workbooks named "Import", "Model" and "Export".

My import file has approximately 1million lines and I have about 34 of these files. Each data set has a varying number of lines around the 40,000 region.

Therefore, I would like to run the macro to do the following:
Import one set of data to the model;
Run the model;
Export the results to the "Export" workbook;
Import the next set of data to the model and so on.

I have developed macros to run the model and export the data but I was struggling with importing each set of data one after the other. That's where you've come in but my coding skills are nowhere near as good as yours and I'm finding it difficult to incorporate your code to work within my macro.

Many thanks for your help

**eddiej90** · 01-06-2016, 11:00 AM

Hi Joe,

The macro you've created is great, however it does stop with a debug error after splitting two data sets at this point: DataSetRowEnd = r - 1

I'll just explain my task to you quickly.

I have a huge set of data that needs running through an excel model, once for each meter and then the results are exported to a separate workbook.

So, I have three workbooks named "Import", "Model" and "Export".

My import file has approximately 1million lines and I have about 34 of these files. Each data set has a varying number of lines around the 40,000 region.

Therefore, I would like to run the macro to do the following:
Import one set of data to the model;
Run the model;
Export the results to the "Export" workbook;
Import the next set of data to the model and so on.

I have developed macros to run the model and export the data but I was struggling with importing each set of data one after the other. That's where you've come in but my coding skills are nowhere near as good as yours and I'm finding it difficult to incorporate your code to work within my macro.

Many thanks for your help

**joe31623** · 01-06-2016, 11:39 AM

Eddie,

I believe the problem is due to the fact that the datatypes I initially defined for some of the variables are Integers instead of a double or long. The reason that causes issues is that Integers can only hold values so big and your number of rows exceeds that. I think you need a long datatype but I'll make it a double just in case (I'm a bit new to VBA). I'll change them and post a follow-up when updated.

Ok, I'll abandon the sub that creates individual workbooks per each sheet from the workbook that has all the data (for now).

-Multiple datasets are on each of the 34 workbooks, correct?

If you have 34 files, it would be easy to loop through all Workbooks in a given directory with a macro to do this all at once. As you can see, I can easily split the dataset into worksheets per Meter ID; however, I am wondering if it would be beneficial to do the following:
For I = 1 To 34 (...so to say)
1: Open Ith workbook (All workbooks stored in the same directory)
2: Split each dataset in Ith workbook N worksheets (where, of course, there will be N datasets in the Ith workbook) ... then there will be N + 1 total worksheets in the Ith workbook)
----->Is it possible the Meter ID is not unique within one workbook?
----->Is it possible the Meter ID is not unique across all workbooks?
3: Create a Subfolder in the directory 34 worksheets are stored
4: In that Subfolder, create N workbooks that contain one set of data per workbook
5: Loop over all 34 workbooks

...the result would be N(I = 1) + N(I = 2) + ... + N(I = 34) workbooks

...then it would be easy to run the model on the subdirectory that contains workbooks which have individual datasets

Again, I'll post again when I've updated what I expect will solve the error mentioned above; however, to avoid future errors, I'd like to know if the above solution would be beneficial and the following.
----->Is it possible the Meter ID is not unique within one workbook?
----->Is it possible the Meter ID is not unique across all workbooks?

Joe

**joe31623** · 01-06-2016, 11:45 AM

Eddie,

Ok, try the updated code above and comment on the potential solution I listed above.

Please let me know how the code I wrote works. If there are any errors, please cite the error number in addition to the line that caused the error.

Joe

**eddiej90** · 01-06-2016, 11:50 AM

Joe,

All the data was originally in one CSV file with about 34million lines. I have split this using some simple software into 34 files of 1million lines. Therefore the meters at the end of each file follow on through to the beginning of the next file. So in short, there are about 33 meter IDs that cross over across files (I was just going move these manually into another file).

If I understand where you are coming from, then it would be great if I could run the whole data set with all 34 files, but splitting these files would result in about 2,000 files.

All meter IDs should be grouped together and unique.

Ed

**eddiej90** · 01-06-2016, 12:04 PM

That code works great, I'll try to incorporate it into the macro I currently have.

**joe31623** · 01-06-2016, 12:23 PM

If there are about 33 unique MeterIDs per file and each MeterID is unique, what we can work towards (with minimal effort) once this works is (modifying what I had above):

If you have 34 files, it would be easy to loop through all Workbooks in a given directory with a macro to do this all at once. I am wondering if it would be beneficial to do the following:

Loop over all workbooks in a directory w/ the following structure to
->For I = 1 To 34 (...so to say), where I denotes the Ith workbook
->->
->Next I
1: Open Ith workbook (All workbooks stored in the same directory)
2: Split each dataset in Ith workbook into N worksheets (where, of course, there will be N datasets in the Ith workbook) ... then there will be N + 1 total worksheets in the Ith workbook, including the Worksheet that contains all of the data)
----->Would you need to loop over the workbooks in a specific order so the data is sequentially fed into your Model workbook?
3: Loop over All Worksheets in Ith Workbook that does not contain all data w/ a structure:
->For j = 1 To N Worksheets Created with the Macro we have developed
->->Copy And Paste data into Model (perhaps test for j = 1 and j = N to see if we need to concatenate last/next WB's data to run the model)
->Next j
4: Run Model, Export Data (already developed)

**joe31623** · 01-06-2016, 12:25 PM

Ed,

I'm glad the code works. Send me a PM or respond to this thread if you're interested in developing this further per what I wrote above or anything else. Also, if you can share the application details, when you have time, please PM me.

I'll check back on this later tonight or tomorrow unless I notice that you have an issue here within the next few minutes.

Take Care,

Joe

**eddiej90** · 01-07-2016, 09:05 AM

The macro is working great but I'm at the point now where its a little time consuming to do all these 34 partition files.

Currently I am opening the Nth partition file, copying and pasting data into the "Import" file, running the macro which dumps results into an "Export" file. Then saving this Export file as "ExportN".

What do I need to add to the beginning and end of the macro in order to loop through all 34 files? I have processed up to the first 8 so far.

The partition files are located in C:\Users\User\Desktop\Pieces and are named Piece_N.csv where N is the Nth file (N=9 to 34 for the files left to analyse)
The export files need to be named ExportN.xlsx and are saved into C:\Users\User\Desktop\Outputs
The original Export file needs re-opening after I do the save as.

Many thanks

**joe31623** · 01-07-2016, 05:22 PM

I can take a look at this problem later tonight or tomorrow morning, but I'll need some more information. I think all I need to know is:
-1: where to paste
--1a: what cell is the beginning of where you want to paste the data when you run your model in the "Import" file?
--1b: what is the sheet name that contains the cell referenced in (1a)
--1c: what is the exact workbook name (with extension -- .xlsm, etc.)?
-2: When the model runs, where does the macro you have output the results?
--2a: I need to know the range of cells the results of your model output (ex: cells from data A1:A4023 output to cells B1:B4023 or B1:B10, or B1:Variable Cell but specify that there are no spaces b/w B1 and the last part of the data)
--2b: remember to specify the sheet name/sheet name format, if different, or if it's the same sheet name referenced in (1b)
--2c: workbook name answered above so ignore 2c, assuming the output of the macro is mapped to the same workbook

**joe31623** · 01-07-2016, 06:12 PM

Oh... I'll also need to know the name of your macro that runs the model b/c I'll have to run the macro from another workbook. In order to do that, I'll need to use some code similar to:

Please Login or Register  to view this content.

Select range of data based on values in colum A

LinkBack

Thread Tools

Rate This Thread

Display

Select range of data based on values in colum A

Re: Select range of data based on values in colum A

Re: Select range of data based on values in colum A

Re: Select range of data based on values in colum A

Re: Select range of data based on values in colum A

Re: Select range of data based on values in colum A

Re: Select range of data based on values in colum A

Re: Select range of data based on values in colum A

Re: Select range of data based on values in colum A

Re: Select range of data based on values in colum A

Re: Select range of data based on values in colum A

Re: Select range of data based on values in colum A

Re: Select range of data based on values in colum A

Re: Select range of data based on values in colum A

Re: Select range of data based on values in colum A

Re: Select range of data based on values in colum A

Re: Select range of data based on values in colum A

Re: Select range of data based on values in colum A

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] How to Select Worksheet and Cell/range based on ComboBox Values

Macro to sum values in one column based on data in another colum while ignoring blanks

[SOLVED] Colum count based ón values In colum a and b

Select unknown range and sorte by colum

How to shift range of values from a colum to another colum at a programmed cell.

[SOLVED] How to select a range based on values in 1 column

Bookmarks

Bookmarks

Posting Permissions