Avatar of Tocogroup
Tocogroup
Flag 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

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
Microsoft Excel

Avatar of undefined
Last Comment
Tocogroup

8/22/2022 - Mon
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.
Rory Archibald

Out of interest, why does your table have blank rows in it? Kind of defeats the point of the table, no?
Tocogroup

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.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Rory Archibald

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.)
Tocogroup

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.
Rory Archibald

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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Tocogroup

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
ASKER CERTIFIED SOLUTION
Rory Archibald

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Tocogroup

ASKER
Many thanks for your help