Forum Discussion
modify VBA code to include dynamic columns instead of static
I suspect that with the newest releases of Office 365 most of the things described here can be done easily with worksheet formulas. The points to observe are that all raw data should be entered into Excel Tables so that the field to be searched for the value "Unknown" can be identified by name rather than a static numeric index. Given that tables adjust dynamically to the data, no counts are required to determine the length of the column.
The key function is FILTER which will return all the records that match the criterion
= FILTER( Table1, Table1[field]="Unknown"} )
The advantage of names is as great when it comes to VBA. A named field can be block-read into a variant array and then the looping is in memory rather than requiring continual exchanges between the VBA environment and the worksheet. If the column changes, the Name moves with it, so the code is more robust.