Navigate through dynamic range

[dma431]

Hi, I would like to move, at the press of a button, through a table's databodyrange cells (to the right, then continue with the next row), but skipping some predefined columns. I have a dynamic named range set up that includes all the table columns I want to move through, but I have no idea how to accomplish this using VBA.
Any ideas?
Thanks.

 

[CountTepes]

Have you tried protecting the worksheet?

But first unlock the cells, to which you want access.

 

[dma431]

Hi CountTepes,
That would be the easy way to do it, but I can't protect it, as it will interfere with the other functions of the worksheet.
Thanks.

 

[CountTepes]

Try this. It will run every time you select a different cell. If you select a cell which is in a column you do not want to edit, then the selection will move to the next column to the right and the code will run again.
If you have consecutive columns you have specified to skip, then it will keep jumping across until it gets to a column which is not in your list.

Worksheet_ChangeSelection(ByVal Target as Range)
Select Case Target.Column
       Case Column1, Column2, Column3, Column4 '    Put in a list of column numbers which you want to skip
             Target.Offset(0,1).Select
End Select
End Sub

 

[dma431]

I didn't have time to test it today. I will get back to you tomorrow. Thank you!

 

[dma431]

Hi CountTepes,
I tried it passing the Target to a Sub and it works, in part, but not as expected. It actually moves through the locked cells, so I invered the column numbers in "Case", but when it reaches a locked column it stops instead of jumping to the next.
The way you described it is what I need, except the part when it gets to the end of the row, as Offset 0,1 will move to the right and not continue with the next row in the table.
I think it will be complicated to make it work. I'm ready to give up, unless you have more ideas.
Thanks for your help.

 

[CountTepes]

A couple of things. Mea culpa. The first line should read:

Private Sub Worksheet_SelectionChange(ByVal Target as Range)

I'll put I'll down to my not concentrating.

By end of row, I assume you mean the end of your data as opposed to column 16384?

This time I have made sure it works. Rather than just rely on my memory.

This code on my PC, will allow me to select cell A1. As I use my arrow keys to navigate, the moment I move into cell C1, the selection moves into Cell F1. As I carry on to cell H1, it jumps to Cell A2.

Private Sub Worksheet_Selectionchange(ByVal Target As Range)
Const Maxcol = 10
Select Case Target.Column
       Case 3, 4, 5, 8, 9, 10 '   Put in a list of column numbers which you want to skip
             Target.Offset(0, 1).Select
       Case Is > Maxcol
            Target.Offset(1, -Maxcol).Select '  This tests if you have selected a cell which is in a column greater than your MaxCol number and move to 1 row down and the first cell
End Select
End Sub

The effect is similar to having locked and unlocked cells on your Worksheet and protecting the sheet and not allowing selection of locked cells.

 

[dma431]

Yes, by end of the row I meant the end of the table range. This works CountTepes, after setting the number of columns.
Thank you!