Yes, the two activities we cover in this session – formatting and clearing data – are not absolutely essential in your weekend process.  Omitting them, however, could cost you a huge amount of time in the long run.  So, can we deal with them now, in way that means we’ll never have to think about them again?  At least, that’s the goal for this session!

First, a macro to ‘clear out’ the previous week’s data is required.  You could easily assume a maximum number of rows (200?) and simply clear that range each week.  I prefer a dynamic solution, however, since I would always worry about the tiny possibility of more than 200 fixtures!  A dynamic solution can provide peace-of-mind and in the video we explore how to do it. Phew …

Cell formatting is a difficult topic in Excel since what appears ‘good’ is always subjective.  I try to think about formatting in a functional way: does it actually help you read the data and process it?  This is called ‘assimilation’ and formatting should support it.  Having created a helpful look, the next challenge is to programme it in VBA.  ‘Well, that’s easy enough with the macro recorder’, you might be thinking.  You might be surprised by my approach that not only saves on code but allows you to make formatting changes without visiting the VBA editor.  Aparajita, anyone? 

Now we’re deep in this project, we’re never too far from a high level discussion.  And there are several in this video including short vs. long macros, static vs. dynamic approaches and more.  How much these discussions matter to you depends on the extent of automation you’re aiming for.  Want a slick and robust ‘click-of-a-button’ process that will serve you well for years?  Then try to get to grips with the main points, and remember to leave your queries below the video – I’ll answer them there.