How to skip cells or columns when tabbing in Excel?
By default, pressing the Tab key in Excel will move from a cell to the next one horizontally. For some Excel users, they tend to skip cells when tabbing in Excel. For example, cell A1 is selecting now, after pressing the Tab key, it will jump to cell C1 directly with skipping the cell B1, and pressing the Tab key again will skip the cell D1 and move to E1 as below screenshot shown. How to achieve it? The method in this article can help you.
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
Please do as follows to skip cells or columns when tabbing in Excel by using VBA code.
1. In the worksheet you need to skip cells when tabbing, please right click the sheet tab, and click View Code from the context menu.
2. In the opening Microsoft Visual Basic for Applications window, please copy and paste the below VBA code into the Code window.
VBA code: Skip cells or columns when tabbing in Excel
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
Note: In the code, [B:B], [D:D], [F:F] are columns you will skip when pressing the Tab key. You can change them as you need, and you can add new column you need to skip into the code.
3. Press the Alt + Q keys to exit the Microsoft Visual Basic for Applications window.
From now on, when tabbing in Excel, the specified column cells will be skipped automatically. At the same time, you can only click these specified columns, the skipped columns will not be selected or edited.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 1 months agoHow can you skip multiple columns at once using this formula? I.e. I want to be able to skip columns A, H and then M to P
- To post as a guest, your comment is unpublished.· 5 months agoit's ok - i've replied to your email in gmail.
- To post as a guest, your comment is unpublished.· 5 months agoHi, this definitely only works on the first two columns of spreadsheet (i.e B:B and D: D), it won't work on the third (i.e H:H) or any subsequent - it must be a limitation? Any way around it? - I have excel 2016
- To post as a guest, your comment is unpublished.· 5 months agoHi Steve,
I have tested the code in Excel 2016, and it works. Do you mind attaching a copy of your data file? Sorry for the inconvenience.
- To post as a guest, your comment is unpublished.· 5 months agosure, if you can give me an email address to send it to? mine is itginternet AT gmail . com.
- To post as a guest, your comment is unpublished.· 9 months agoThe code only works on the first 2 rows in the spreadsheet - Once I wrap around to the 3rd row then the designated "Skip" cells are no longer skipped - So close on this please advise. Been looking for this code forever - Thanks
- To post as a guest, your comment is unpublished.· 6 months agoHi Lonnie Nagel,
I try the code and it works on the whole worksheet. Which Excel version are you using?