What is fastest for this? Small VBA or many worksheet formulas...?

[Maria J-son]

Hi,

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

 

[K Dales]

I think you can use SpecialCells as a way to check; so a vba routine (your
Worksheet_Change procedure, maybe?) could do for any rows you specify; if you
want to check all the rows in the current Target range:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim CheckRow as Range, ErrorCells as Range
....
For Each CheckRow in Target.Rows
ErrorCells =
Intersect(CheckRow.EntireRow,ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, xlErrors))
If Not ErrorCells Is Nothing Then...
' This would indicate there are errors on the row being checked
' Put your code here to deal with it
End If
Next CheckRow
....
End Sub

Haven't tested this extensively but should run fairly quickly I would think.