Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Maria J-son
 
Posts: n/a
Default What is fastest for this? The Small VBA or many Worksheet Functions...?

Hi,
(This is also posted in "ms.public.excel.programming")

FACT:
I have tables consisting of 20 "batches" of 20 "undergroups" with 10 rows in
each undergroup. All of them are under eachother. In the row there is 3
columns of text cells and 20 columns of numbers

You choose what batch to show in a worksheet data validation list and how
many undergroups you want to show. Only five rows in each undergroup shows
from start and when typing data in a cell at 5th row, a sixth will appear
etc. If the 5th and 6th both are empty, the sixth will be hidden again.

PROBLEM DESCRIPTION:
The application is built on by not showing the main part of the rows. I want
to have a cell data validation to inhibit any action leading to hide rows if
that row has error values. It would trig on Worksheet_change event.

At first I thought of having a short vba code to check a cell to the right
of the columns. That cell could check the row with ISERR etc. formulas.
Worksheet functions are faster, right?. But this lead to have functions at
every row and i guess that there's a lot of calculation there...so I wonder
if a vba check when "batches", "undergroups" and rows would be faster?

QUESTION IN SHORT
Small vba checking worksheet formulas, but there has to be formulas on every
row.?
OR
VBA code running through every cell (the largest common case is "batch"
change with 20 x 10 rows = 200 rows with 23 cells in each = 4600.

And how will it become if a future application may be needed to check all
cells in the worksheet (20*200 rows = 4000 rows with 23 cells in each =
92000 cells...

POSSIBLE OTHER SOLUTIONS?
Any experience would be welcome! Maybe you know a faster way:
- Only checking the nonblanks with SUMIFor something?
- Specialcells in VBA etc.?
- Create worksheetfunctions with VBA code on the needed rows only, check the
Worksheet function and then delete them again?


/Kind regards


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
small help can save mee!!!!!!!! taesha Excel Discussion (Misc queries) 0 July 27th 05 09:26 PM
How do I compress small Workbook which shows 30 MB in size? netgd Excel Discussion (Misc queries) 1 May 23rd 05 09:26 PM
Office Small Business ver. 2003 has problems running in XP pablo5551 Excel Discussion (Misc queries) 0 April 26th 05 02:14 AM
feasibility study for small business El Rasheed Excel Discussion (Misc queries) 1 March 19th 05 06:05 PM
Small font on row & column numbers - wierd Mike R. Excel Discussion (Misc queries) 1 December 4th 04 01:32 PM


All times are GMT +1. The time now is 01:19 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"