Link to home
Start Free TrialLog in
Avatar of Tocogroup
TocogroupFlag for United Kingdom of Great Britain and Northern Ireland

asked on

What is the most efficient way to process an Excel table in VBA, row by row

I want to process each row of an Excel 2010 table of 200 rows using VBA. What is the most efficient way of coding this. A For....Next loop ? How can I use the table name to reference a cell  ?  If only 3 rows in the table have data (the remaining rows being blank), how can I detect this and stop the loop ?
Avatar of StephenJR
Flag of United Kingdom of Great Britain and Northern Ireland image

What exactly do you mean by "process"? It doubt it will make much difference on a table that size but generally quicker to transfer to an array, do things there and then transfer back to the spreadsheet.
Avatar of Rory Archibald
Out of interest, why does your table have blank rows in it? Kind of defeats the point of the table, no?
Avatar of Tocogroup


Ok...this was the requirement : Create a table (with a table style of alternating coloured rows) which allows the user to enter from 1 to 200 rows of data. A couple of the columns are protected so the user can't insert or delete rows.

I've included validation (dropdown lists, value ranges etc) in some of the columns. The workbook is 'submitted' by clicking on a button which then performs some cross-validation within each row. Hence my using VBA to 'process' or check each row. When I find a cross-validation error then I highlight the cell(s) and display a Message Box with a relevant alert. Only when the table is completely validated is it saved to disk.

So, yes, I'm using a table in unorthodox fashion but it serves the purpose of presenting multiple rows for data entry.
Do you have a key column that should always be populated? If not, you'll have to loop through every row and check the count of cells (since in theory they could leave blank rows in the middle of the data.)
I have a mandatory field which determines whether the rest of the row is validated or otherwise. I guess they could miss out rows though.
Assuming the field is in column A, you can use:
Cells(rows.count, "A").End(xlup).row

to get the last populated row in col A, then loop from 2 to that number. To be honest, with 200 rows, as Stephen said, it's probably not going to make a lot of difference even if you loop every row, but you could also use something like:
For each rngcell in Range("A:A").SpecialCells(xlcelltypeconstants)

to only loop populated cells in column A.
Thanks for the 'For each' construct. That's useful to know.

Should I be referencing a table differently, that is, by it's defined name, rather than by its column/row references ? In other words, is this a practice you follow ?

Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Many thanks for your help