Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
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 |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
small help can save mee!!!!!!!! | Excel Discussion (Misc queries) | |||
How do I compress small Workbook which shows 30 MB in size? | Excel Discussion (Misc queries) | |||
Office Small Business ver. 2003 has problems running in XP | Excel Discussion (Misc queries) | |||
feasibility study for small business | Excel Discussion (Misc queries) | |||
Small font on row & column numbers - wierd | Excel Discussion (Misc queries) |