Select only one column even if a merged range lies below

Question

Test case:

Take an empty sheet, and merge the range "D2:F2". You can do this manually.

Then, activate the macro recorder and select the column E by just clicking on the E letter on the top of the spreadsheet. You will get the following:

Columns("E:E").Select

Now, try to run this line of code from the same macro directly: you will see that it selects the three columns D, E and F.

Question:

Is this a bug of the macro recorder? Or, rather, a bug of VBA itself (that detects the merged range in my column and decides to extend the selection even if explicitly asked to select one single column)? How should I do to select only one of the columns on which a merged range lies via VBA code, exactly as I can do manually?

Need:

I have a spreadsheet with year on a line, months on the below line and days on the below line. Hence, the days are just cells but months and especially years are shared/merged cells among the several days.

My need is just to detect the current day and select the column, in order for the user to see on which day they should look the data at. But, because of the "years" cell widely merged just above, the entire year is selected.

Answer 1

No, this is not a bug.

Why: Try to manually select the range E1 to E5. That is what is going on when you use Columns("E:E").select. Think of it as .Select not selecting the column, but instead selecting each cell from top to bottom.

The .select method isn't something you should depend on. What exactly are you trying to use select for? There is another (quite arguably better way) to do this.

Edit: Also, as my father always says, merged cells shouldn't be used. He uses "center across selection" instead, which looks exactly like a merged cell without any of the seemingly buggy behavior.

Need: I would use the macro to highlight the data... probably with something like this...

Range("E7").Interior.ColorIndex = RGB(0, 0, 0)

Answer 2

You probably know the row in which the days start. Therefore, instead of selecting the entire column, you could define a range starting from the first day row to the last day row and select that range.

Answer 3

REQUIREMENTS:

Your table should have this values and formats

Then you can loop through each column on row 4 -just assumed- and check each value if they match today. Next you can scroll to that cell using Application.Goto.

CODE:

Sub FindToday()
  Dim wsTable As Worksheet  '<~ worksheet with your table
  Set wsTable = Sheet2
  Dim Cols As Integer       '<~ a variable to loop through columns
  With wsTable
    For Cols = 1 To .Cells(4, .Cells.Columns.Count).End(xlToLeft).Column + 1
      If .Cells(4, Cols).Value = Date Then  '<~ check if the date is today
      Application.Goto wsTable.Cells(1, Cols), True '<~ scroll to that cell if true
      Exit For
      End If
    Next
  End With
End Sub

Answer 4

I feel that the question is genuine unlike some of the comments here. I will try to explain.

Using the test case from the question, say I want to do some action only on column D (say change its column width), without changing the same for columns E to F. I can do that in excel by selecting column D specifically by pressing on column header (press on that "D" in the column names bar). If we select column using range selection (mouse or keyboard shortcut CTRL+SPACE), it extends the selection to include E and F columns. But if we press that column D on the header, it only selects one column. I expect VBA to do the same.

Sadly, I couldn't find anything to "select" a single column or range which includes cells merging through multiple columns or range. However, I could do the action on that single column.

I tried following that didn't work. And I feel that it should work.

Range("D:D").Select

Didn't work. Extends the selection to include merged cells. I guess, this is okay.

Columns("D").Select

Didn't work. Extends the selection to include merged cells. I feel this is not okay.

Columns("D").EntireColumn.Select

Even this didn't work. This definitely should've.

So finally I directly applied the action without selecting the cells.

Column("D").ColumnWidth = 10

And this did it. Only the column D width was changed, leaving column E and F untouched. Similarly, I could do font change and other actions.

Only drawback is that I have to do all actions individually. So, I use a loop to perform action on the selection.

Something like this:

For Each x in Range("D:D")
    x.font.size = 10
    x.font.name = "Calibri"
    '...and so on...
Next x