Forum Discussion
Excel Question - Skipping Columns
I hope someone can help with my question. On my spreadsheet, I would like to skip over certain columns when entering data. I was able to set it up when I don't want to enter data at all using the following code (which, quite frankly, I have no clue what it all means, but I managed to make it work):
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static sRg As Range
Dim ColumnOffset As Integer
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Union([B:B], [D:D], [F:F])) Is Nothing Then
With Target
Application.EnableEvents = False
If Not sRg Is Nothing Then
If sRg.Column < .Column Then
ColumnOffset = 1
ElseIf .Column <> 1 Then
ColumnOffset = -1
End If
Else
ColumnOffset = 1
End If
.Offset(, ColumnOffset).Select
Application.EnableEvents = True
End With
End If
Set sRg = ActiveCell
End Sub
There is probably a simpler way to accomplish this,..if so, please feel free to make suggestions. What I don't know how to do is skip over columns where I don't want to enter data based on another column's entry I make. For example: I enter N/A in column A. Columns B and C are set up to automatically add N/A if there is an N/A entered in column A using an IF formula. Is there a way to have excel skip over columns B and C and go directly to D when N/A is entered in column A? But if something other than N/A is entered in column A, I want to be able to enter data in columns B and C.
1 Reply
I read what you've written and find myself wondering a number of things:
- Is this always happening (when it happens) with the same column, or same set of columns?
- How many columns over all are there?
- What's the purpose of the spreadsheet in the first place?
That's just for starters. It definitely would seem to me that using a VBA routine to do what two touches of a tab key can do is killing a mosquito with a bazooka. (But I'm averse to VBA in general, believing that astute use of formulas and good design can accomplish a lot; my bias is not absolute, but I definitely don't turn to VBA/macros unless absolutely necessary.)
Bottom line, I just wonder if you can re-order the columns (or do some other kind of re-design) so the ones that often get by-passed come later, and only need to be touched by the cursor when they're needed.
Is it possible for you to post a sample of the spreadsheet? Can you describe the "business purpose" of it, or some other kind of background as to what the bigger picture is?
