Select only one column even if a merged range lies below

user_3111149 0
Viewed 6793

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. enter image description here

vba excel
5 Answers

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
user_109161360

If you want just to hide the particular column if there is merged cell try not to select the column just use like this for example -- Columns("N").EntireColumn.Hidden = True... This will solve your doubt.

user_180905630
Related