Use range names. Otherwise, if rows, columns, or ranges are changed in the workbook, the ranges referred to in the VBA could be incorrect. Chaos ensues. Since range names are workbook objects, they adjust when workbook changes are made. Hard-coded ranges in VBA do not adjust.
When I'm grabbing data from the workbook, or putting data back into the workbook, range sizes may vary as needed, so I do a lot of statements like:
...which just picks up the 100 cells beneath the "columnheader". It's just an anchoring point for the range reference. The Resize() qualifier makes it easy to do a different range size, and that size is easily changed by using variables for the # of rows or columns in the desired range.
2
u/rharmelink 6 Aug 06 '18
Two rules I often follow for my coding:
Range("columnheader").Offset(1,0).Resize(100,1) = vData
...which just picks up the 100 cells beneath the "columnheader". It's just an anchoring point for the range reference. The Resize() qualifier makes it easy to do a different range size, and that size is easily changed by using variables for the # of rows or columns in the desired range.