Split LARGE Workbook into multiple WBs

[adambc]

I've got a LARGE WB (an extract from a legacy system that's been switched off!) that I need to process but it's soooo slow!

Plan is to spilt it into multiple WBs (manual test shows it will perform better as smaller WBs) ...

Every record has a 6 digit ID (NOT a record ID so there can be multiple rows per ID) and I want to split the file by ID range so that all records for a particular ID are in the same file ...

Is there an easier way than doing it than manually (which feels a bit tedious!)?

Thanks ...

 

[jkpieterse]

What exactly is slow? Is it slow to open, slow to calculate, what?

 

[adambc]

What exactly is slow? Is it slow to open, slow to calculate, what?

Both, it’s currently c 500MB, 500000+ rows, 165 columns

 

[jkpieterse]

What kind of calculations are you doing that make it slow to calc? Perhaps a different approach may help?

 

[adambc]

What kind of calculations are you doing that make it slow to calc? Perhaps a different approach may help?

It’s 1 of 70 extract files that I need to loop through with a known ID and copy rows containing the ID in a specific column to a new WB/WS …

All the other extract files are “sensible” sizes and each one takes seconds to open, copy the data and close whereas this file takes forever (I haven’t timed it but it runs into many minutes, maybe 20)

I can’t access the VBA right now but it’s along the lines of …

For i to lastrow
If (eg) CFi = ID Then
Copy entire row to target WB/WS
End If

 

[jkpieterse]

I would rewrite the code so it:
- Generates a unique list of IDs
- Runs through them
- Filters the table for each ID
- Copies visible rows

 

[adambc]

I would rewrite the code so it:
- Generates a unique list of IDs
- Runs through them
- Filters the table for each ID
- Copies visible rows

My fault (trying to post from my ‘phone!) but not sure I’ve explained what I’m doing properly …

Will update when I can get to a proper keyboard (and post my VBA) …

But the problem remains that the file takes a LONG time to open, whereas manually creating a file with eg 50000 rows was in line with the other extract files (and yes I know, there would be 10 files to process!) …

I could split the file manually but was looking for an easier alternative - but if there’s a better way to process the large file I’m all ears …

Watch this space for my VBA …

Thanks …

 

[jkpieterse]

If you can give us an idea of what you are doing with the data (calculation-wise), perhaps we can point at alternative approaches