Linker IT Software
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
VBA prog'ing help (Read 4848 times)
YaBB Newbies


Posts: 1
VBA prog'ing help
04.07.08 at 08:40:02
I have a VBA programming problem. I have a data with over 200000 rows. But limitation in excel for over  
65000 rows doesn't allow to fit data in one excel sheet. So i splitted the data in 20000 rows chunks in seprate sheet.  
But now i have one problem : I have one column, column_nbr, that has value 1 to 99.  
I want when data get splitted one column value should not distributed in 2 worksheet .  
i.e., If sheet 1 has data for column_nbr 1 to 20 then for col 20 all values should be in same sheet  
and same time maintain the condition of 20000 rows in one sheet only.  
Can anybody help me for code for above problem...
Thanks in advance
Back to top
  IP Logged
Gerrit-Jan Linker
YaBB Administrator

Posts: 75
Re: VBA prog'ing help
Reply #1 - 09.07.08 at 15:02:51
I would try to not solve this problem with VBA but using normal SQL instead.
Try to split your dataset into chunks that fit into one sheet. If the key is your column_nbr you can find the mininum and maximum with:
select min(column_nbr), max(column_nbr )  from table  
Then try a value in the middle of the range (e.g. 50) to split the data:
select * from table where column_nbr <= 50;
select * from table where column_nbr > 50;
You can use the count option to see how many rows will be fetched.
Back to top
« Last Edit: 09.07.08 at 15:06:06 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1