Macro to Freeze Values of Cells in Present Column, Move Right, Repeat

In the attached Test.xlsm, I have columns containing formulas. I would like to set up a macro that will freeze those formulas (i.e., convert to fixed values): freeze the next 10 columns, save, move to the 11th column, repeat. So far, my macro looks like this:

Please Login or Register  to view this content.

Questions and problems:

1. I don't know how to specify the next 10 columns relative to the present point. This code refers to columns A through J because I started in cell A1. But how can I get it to refer to the next 10 columns after I've moved to cell K1?
2. After freezing the next 10 columns, I need to move to the 11th column, same row. At present, I have Range("K1").Select. Should I use something like ActiveSheet.Cells(11,0).Select ?
3. Ideally, to stop when it reaches a column with no data on row 1, I think I should use Do While - Cells(i,1).Value = but I'm not sure how to represent a null value.

I'm not sure why the need to do this in chunks of 10 columns rather than just the whole lot in one go, but give the below a try and see it if works as required.

Please Login or Register  to view this content.

BSB

Thanks, BSB. Much more elegant than what I had. I have to admit it mystifies me. But it works.

I guess you figured out that my uploaded sample spreadsheet inadvertently has the formulas already converted to values in the first 10 columns.

The key missing piece is the save after each 10 columns. I wanted to pause after each save, to view cell contents and verify it's working as intended, so I tried this variation on your code:

Please Login or Register  to view this content.

but (a) it's annoying to have to add so much clutter and (b) the resulting input box doesn't allow me to inspect cell contents, to see whether ActiveWorkbook.Save is working as intended.

I saw somewhere that a backslash can be used to insert a simple pause (or I guess the correct term is Stop) in macro execution, but I couldn't quite find out how to make that work.

If all you want to to is stop the code running temporarily, put your cursor on the line of code you wish to pause at and press the F9 key to insert a break point.
The code will run until that line and you can go examine the results, then when you wish to start it again just press F5 whilst in the VB editor.

BSB