VBA Merging Cells Help Needed

[Tuffman]

I am trying to merge two cells that will be next to each other using VBA. I need to keep the row constant but the columns will change. I have tried using the two lines of code, but neither of them work. I would appreciate any insight into getting these to work. Thank you.

Worksheets("GT Quick Reference View").Range(Cells(1, j), Cells(1, j + 1)).Merge

Worksheets("GT Quick Reference View").Range("A" & j:"A" & j + 1).Merge

 

[6StringJazzer]

Single letters are not good variable names. They don't indicate anything about what they mean. What is j? In the first line of code you are treating it as a column. In the second one, you are attempting to treat it as a row, but your syntax is incorrect.

These two lines of code give correct syntax and they will merge cells, but without more information I can't know if this is what you want to.

   Dim RowNum As Long
   Dim ColumnNum As Long

   ' Set RowNum and ColumnNum to...something

   Worksheets("GT Quick Reference View").Range(Cells(1, ColumnNum), Cells(1, ColumnNum + 1)).Merge

   Worksheets("GT Quick Reference View").Range("A" & RowNum & ":B" & RowNum).Merge

Oh, and don't do this. Merging cells is a very undesirable practice.

 

[Tuffman]

Thank you for your quick reply. I apologize, I should have explained that the "j" is an integer that I will have increment. I initially set j = 1 and then have a j = j + 2. Between them I have my merge statement that I cannot get to work.

I tried your first suggestion and renamed j to ColumnNum and set it as long. However I could not get it to work. How would I rewrite your second recommendation if the rows are always Row 1 and the Columns are the ones that increment?

And I have to get the the cells merged. Sorry. Thanks again for your help.

 

[jolivanes]

It's weekend so we might have time to read helpful information.

Excel: Don’t Merge Those Cells! Here is an Alternative. – Office Bytes

 

[6StringJazzer]

Like I said, in one case you were using j for a column and the other for a row, so I did not know your intent in setting j.

If the row is always 1 and the columns increment, ignore the second recommendation, use the first. My second recommendation was just to fix your syntax not knowing what you were actually trying to do.

 

[Tuffman]

Jeff,
I really appreciate the point you made regarding being more descriptive with my variables and I have implemented that change. So thanks for that.
Below is my code in total, but I cannot get the merge part to execute. Do you see something wrong that I may be overlooking? Don't worry about the number for the 'SearchRange' variable. It's a 3863 row sheet of data, so I'm just running it on a few rows to see if it works.

Thank you,
Russ



Sub GTQuickReference()
Dim ColumnNum As Integer
Dim RowNum As Integer
Dim SearchString As String
Dim MaxRowNumber As Integer
Dim SearchRange As Integer

SearchString = "GT"
MaxRowNumber = Worksheets("Test Design").Cells.SpecialCells(xlCellTypeLastCell).Row
SearchRange = MaxRowNumber - 3818

ColumnNum = 1

For RowNum = 3 To SearchRange

If Worksheets("Test Design").Cells(RowNum, 2).Value = SearchString Then

Worksheets("Test Design").Select
Worksheets("Test Design").Cells(RowNum, 1).Copy

ColumnNum = ColumnNum + 2
Worksheets("GT Quick Reference View").Select
ActiveSheet.Paste Destination:=Worksheets("GT Quick Reference View").Cells(1, ColumnNum)
Worksheets("GT Quick Reference View").Range(Cells(1, ColumnNum), Cells(1, ColumnNum + 1)).Merge

End If

Next RowNum

End Sub

 

[jolivanes]

You need to qualify properly.

Worksheets("GT Quick Reference View").Range(Worksheets("GT Quick Reference View").Cells(1, ColumnNum), Worksheets("GT Quick Reference View").Cells(1, ColumnNum + 1)).Merge

Or
at the top declare

Dim ws2 As Worksheet
Set ws2 = Worksheets("GT Quick Reference View")

more code here

ws2.Range(ws2.Cells(1, ColumnNum), ws2.Cells(1, ColumnNum + 1)).Merge

I did not go through all the code but the above stood out for me.
Try the changes and let us know either one way or the other.

 

[jolivanes]

This is one of my many requests and on occasion people get upset. If so, I'm out
Going through and deciphering code that does not do what you think it should is time consuming and people have to guess what is meant.
And also, everybody does things different. Remember the saying about all the different roads leading to Rome.
If you can, explain in a concise manner what needs to happen.
A quick scan of your code shows that it should be considerable shorter and more to the point.

Don't think that the above is a negative comment. It is not. All of the people were at the same stage when they started.

 

[jolivanes]

I forgot to ask.
Is the first time pasting to be done in Range("C1") and not in Range("A1")?

 

[6StringJazzer]

You need to qualify properly.

This is an excellent point that I missed. Apologies for leading down the wrong road.

Another possibility:

With Worksheets("GT Quick Reference View")
   .Range(.Cells(1, ColumnNum), .Cells(1, ColumnNum + 1)).Merge
End With