Excel VBA .Cells for Beginners Part 3 of 4

The Excel VBA .Cells technique is incredibly powerful - and slightly confusing! Learn it and use it with this series of four videos.

Link to download file:

So far in the .cells series, we have dealt with how to work with a single cell selection. No doubt this is useful (and fun!), but selecting ranges consisting of multiple cells presents new possibilities: it might allow us to work with datasets that change in size, for example - a common requirement in Excel projects. So, how would we select a range of cells using the .cells technique?

The key syntax is Range(Cells(a,b),Cells(a,b)) where the first cells construct represents the top-left cell of the range, and the second the bottom-right cell. This incredibly powerful construct allows us to do so much - but is very tricky to code. If you try to write it out without a steady and systematic approach, you are likely to run into trouble and get frustrated! Chris has been there so many times... :-(

To avoid this, Chris builds up the code step-by-step, starting with a conventional range reference in VBA (eg. range("A1:A10")) that you are probably familiar with. After testing this, we can substitute in the more complicated syntax, once piece at a time, testing at each step, whilst steadily building up the final construct. Chris demonstrates in the video - work along with him, and don't forget to do your own experimentation! 'Play' is key.

Quotable:
"Whenever I deal with complicated syntax, I simplify it; write something simpler first, then build it up."

In the final video, we will aim to substitute the hard-coded values in the VBA editor for cell references. Can you imagine the kind of functionality that might result? The possibilities are mind-boggling. See you in the final video!

EXCEL VBA .CELLS SERIES RELEASE DATES

Playlist link:

These introductory videos will be supported by 'long-play' application videos showing how to apply the technique to real-world problems. These will be viewable in the series playlist when released.

Get in touch with Tiger using the platforms below!

Mailing List

Also on Insta :-)

[CarlosSanchez-xzxv]

Nice video Chris ! Thanks for VBA-ing us. Cheers.

[rayhanrana]

Thanks, Chris!!! for creating awesome video

[farzadn]

Hi Chris,
Thanks for your awesome videos. Actually I have an issue with my spreadsheet that using circular iterative calculation and couldn't find an answer in internet for that. I appreciate if you can help me.
in the formula sometimes I get #DIV/0! that is usual and I should change some parameters. But when I fix those parameters still I have this error and I have to manually set the iterative calculation in the cell. Is there a way that formula recalculate again by itself?
Thank You!