Written by Allen Wyatt (last updated December 21, 2019)
This tip applies to Excel 97, 2000, 2002, and 2003
Say you have a blank worksheet and the range A1:F1 has the "merge and center" format applied to it. If you select column B by clicking the column heading, Excel dutifully selects column B and makes cell B2 the active cell. This behavior was modified in either Excel 2000 or Excel 2002; in previous versions of Excel you get the merged cell (A1:F1) included in the selection.
Apparently, VBA trails somewhat behind the behavior of the user interface, as selecting the entire column B also ends up selecting all the columns, A through F:
Sub TestMacro1() Range("B3").EntireColumn.Select End Sub
There seems to be no way around this behavior. Even if you eliminate the EntireColumn method and simply select column B, you still get all the columns, A through F:
Sub TestMacro2() Range("B:B").Select End Sub
It is probably a better programming approach to not select the column preparatory to doing some action upon that column, but to do the action directly. For instance, let's assume that you want to make all of the cells in column B bold. You can do so in this manner:
Sub TestMacro3() Range("B3").EntireColumn.Font.Bold = True End Sub
This affects only the cells in column B, and nothing in A or C through F. You could similarly use an iterative approach to process the cells in the desired column:
Sub TestMacro4() Dim rCell As Range Dim X As Long X = 1 For Each rCell In Range("B:B") rCell.Value = X X = X + 1 Next End Sub
This stuffs a value into each cell in column B, and conveniently ignores any merges that include a cell in column B.
If it is mandatory that you be able to select an entire column, without any columns added because of merged cells, then you may be tempted to use the MergeCells property to check for the merged cells. According to the VBA online help, the following should detect the merged cells in the selection and then dump out of the macro:
Sub TestMacro5() Range("B3").EntireColumn.Select If Selection.MergeCells Then Exit Sub End If ' ' Perform rest of macro ' End Sub
Unfortunately, testing shows that this code will not work. The MergeCells property apparently only returns True if the entire selection is made up of merged cells, not if the selection only contains a few merged cells. That means that you are left to some other way to determine if merged cells have modified the intended selection, such as the following:
Sub TestMacro6() Range("B3").EntireColumn.Select If Selection.Columns.Count > 1 Then Exit Sub End If ' ' Perform rest of macro ' End Sub
This approach examines the number of columns in the selection, and then dumps out if Excel reports that there is more than one.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3093) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Selecting Columns in VBA when Cells are Merged.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
Need to use a macro to select a specific cell in a different workbook? It's not as straightforward of a proposition as ...
Discover MoreNeed a quick way to change the default drive and directory in a macro you are writing? Here's the commands to do it and a ...
Discover MoreCopying information using a macro is rather simple, although there are multiple ways you can do the copying. The most ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2025-01-16 12:17:41
Jerry
Maybe it will be useful for someone...
My problem was when I wanted to unlock/lock cells in a column that contains some merged cells.
this works:
Range("J5").EntireColumn.Font.Bold = True
but this doesn't work:
Range("J5").EntireColumn.Locked = True
I finally managed to get around it like this:
Sub Lock()
For Each c In Range("J5:J15").Cells
If c.MergeCells Then
Else
c.Locked = True
End If
Next
End Sub
2024-03-13 03:37:10
Sterling
Thank you very much!
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments