What is the most efficient way to process an Excel table in VBA, row by row
Hi,
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 ?
Thanks
Toco
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 ?
Thanks
Toco
StephenJR
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.
Out of interest, why does your table have blank rows in it? Kind of defeats the point of the table, no?
ASKER
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.
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.)
ASKER
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(
to only loop populated cells in column A.
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(
to only loop populated cells in column A.
ASKER
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 ?
Thanks
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 ?
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Many thanks for your help