Merge consecutive cells referenced with variables only-CodePudding

I want to merge 4 consecutive cells of a row in a VBA macro/module. However, I am only allowed to use the variables referencing the start cell and/or any other cell (of course, only the cells that need to be merged are of importance- the point being at any stage, I prefer not using the cell address since it is to be used in a Do-While loop, finally).

I have tried multiple methods, but none return the desired merging. Here's what I tried (note- D is the variable for my referenced cell)

Range(D, D.Offset(0, 1)).merge
Range(D & D.Offset(0, 2)).merge
Range(D & D.Offset(0, 3)).merge

This one doesn't return an error but merges only two consecutive cells. On the other hand, the following one, I found it as a solution on multiple sites but it returns "Run-time error: 1004 Method 'Range' of Object'_Global' failed", results in no merging and the loop ends prematurely.

Set E= D.Offset(0,3)
Range(D & ":" & E).merge

D has been set and defined correctly as the loop works with the merge commands commented out. Have only started coding in VBA recently, any help would be great!

CodePudding user response：

There are many ways to do so, I would suggest to use the Range.Resize method.

Assuming that D holds the correct cell (you should consider to use better names, btw), simply use

D.Resize(1, 4).Merge

The Resize-method will return a range with 1 row height and 4 cells width, and the Merge will simply merge all the cells.

The attempt to use a second range variable to hold the last cell is also valid, however you are using the wrong syntax. To create a Range from a start and an end cell, you pass both cells as parameter.

Set E = D.Offset(0, 3)
Range(D, E).Merge
' Same could be done with one line:
Range(D, D.Offset(0, 3)).Merge