Deleting columns via VBA, the row with merged cells which also locate in these columns is hidden

[GoGoUp]

Hi everyone,

I find a strange issue, but could not find the reason.

First of all, there are 3 command buttons with 3 VBA codes.

• CommandButton1 is used to delete Column M and Column N,

• CommandButton2 is used to show rows from Row 16 to Row 1048576.

• CommandButton3 is used to hide rows from Row 16 to Row 1048576.

Code: Select all

Private Sub CommandButton1_Click()
Columns("M:N").Delete SHIFT:=xlToLeft
End Sub

Private Sub CommandButton2_Click()
Rows("16:1048576").Select
Range("A16").Activate
Selection.EntireRow.Hidden = False
End Sub

Private Sub CommandButton3_Click()
Rows("16:1048576").Select
Range("A16").Activate
Selection.EntireRow.Hidden = True
End Sub

Besides, there are two cells in Column M and Column N are merged, such as M3 and N3.

When I click CommandButton3 first to hide the rows, then click CommandButton2 to delete columns of M and N, then Row 3 will be hidden automatically. Following link would show you the gif.

https://1drv.ms/u/s!AnHoiCikfd80hWHQ9O4 ... 3?e=JfhFjw

Has anyone seen a similar problem? Could anyone find the reason and give me a solution? Thanks in advance.

[HansV]

That is very weird behavior! I can reproduce it, but I don't know what causes it.

As a (hopefully temporary) workaround, you could unhide rows 1 to 15 in the code for CommandButton1:

Code: Select all

Private Sub CommandButton1_Click()
    Columns("M:N").Delete
    Range("A1:A15").EntireRow.Hidden = False
End Sub

[GoGoUp]

Hi HansV,

I am glad to hear from you.

Yes, it is a weird behavior. Currently, I would not insist on why and use the workaround.

Thank you very much .

Besides, stay safe under the special circumstances.

[HansV]

Stay safe too!

[Jan Karel Pieterse]

It is odd indeed. The hiding of row 3 also happens if you manually delete columns M and N.
I see this as just another reason to avoid merge cells.