How do I merge cells in another worksheet?

Question

I have a macro that pulls data from five worksheets and aggregates them in two new ones. Currently my code loops through columns in my first new worksheet and merges and centers. However, I am having trouble using the same block of code to do this for my 2nd worksheet. It looks like the code is specific to the active worksheet and I am trying to set it to my other worksheet.

Dim varTestVal4 As Variant
Dim intRowCount4 As Integer
Dim intAdjustment4 As Integer

ActiveSheet.Range("D1").Select
While Selection.Offset(1, 0).Value <> ""
    intRowCount4 = 1
    varTestVal4 = Selection.Value
    While Selection.Offset(1, 0).Value = varTestVal4
        intRowCount4 = intRowCount4 + 1
        Selection.Offset(1, 0).Select
        Selection.ClearContents
    Wend
    intAdjustment4 = (intRowCount4 * -1) + 1
    Selection.Offset(intAdjustment4, 0).Select
    Selection.Resize(intRowCount4, 1).Select
    With Selection
        .Merge
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
    End With
    Selection.Offset(1, 0).Resize(1, 1).Select

Wend

FYI - in most cases, it is considered bad practice to merge cells. Before you run with the idea, consider if there are better ways to get to end result — urdearboy, Feb 18 '19 at 17:26
Unfortunately its mandatory for my current project. Any ideas how to set to another sheet? — Chris, Feb 18 '19 at 17:38
I would avoid relying on `ActiveSheet` and `Selection`. Explicitly state your target ranges & sheets. Highly recommend implementing methods outlined in link provided by @Cyril — urdearboy, Feb 18 '19 at 17:39
Avoid .select and .activate... see https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba — Cyril, Feb 18 '19 at 17:39
@urdearboy Sorry, we were posting at the same time, though I stopped to lookup the link which took me... like 5 seconds longer to post than you! — Cyril, Feb 18 '19 at 17:53
@Cryil If there was any comment that deserves to be repeated, it's that one ;) — urdearboy, Feb 18 '19 at 17:54

Cyril · Answer 1 · 2019-02-19T14:58:17.003

1

Try using with statements to help direct what is going on, such that:

With Sheets("NameOfSheet")
    While .Cells(2,"D").Value <> ""
        intRowCount4 = 1
        varTestVal4 = .Cells(1,"D").Value
        While .Cells(2,"D").Value = varTestVal4
            intRowCount4 = intRowCount4 + 1
            .Cells(2,"D").ClearContents
        Wend
        intAdjustment4 = (intRowCount4 * -1) + 1
        With .Range(.Cells(intAdjustment4, "D"),.Cells(intAdjustment4, "E")) 'VERIFY THIS IS THE INTENDED RANGE
            .Merge
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
        End With
    Wend
End With

Do you want to do a check before assigning varTestVal4 to see ISNUMERIC? You can then Dim that variable as long or integer.

Edit1: Changed range intended to merge as columns A to C on the row indicated by intAdjustment4:

With Sheets("NameOfSheet")
    While .Cells(2,"D").Value <> ""
        intRowCount4 = 1
        varTestVal4 = .Cells(1,"D").Value
        While .Cells(2,"D").Value = varTestVal4
            intRowCount4 = intRowCount4 + 1
            .Cells(2,"D").ClearContents
        Wend
        intAdjustment4 = (intRowCount4 * -1) + 1
        With .Range(.Cells(intAdjustment4, "A"),.Cells(intAdjustment4, "C"))
            .MergeCells = True
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
        End With
    Wend
End With

edited Feb 19 '19 at 14:58

answered Feb 18 '19 at 17:52

Cyril

6,448
1
18
31

This looks like it will solve my issue. But, the line with the comment is has an error. "VERIFY THIS.... Expected list separator or ) I cant seem to get it to run – Chris Feb 18 '19 at 18:07
@Chris what did you replace `"D"` and `"E"` with? – Mathieu Guindon Feb 18 '19 at 18:21
I've tried a few different things. I tried using A for both, A and B, A1 - A16, ect.. I am just trying to merge and center column A and once I figure that out I can do columns A through C – Chris Feb 18 '19 at 18:24
I had to add in a ) as well to make it run – Chris Feb 18 '19 at 18:25
@Chris glad that worked for you. for simply some background in what is going on with that line the overarching range you want to act on from the starting cell to the final cell, will be listed, e.g. A2:E16 would be displayed as Range(Cells(2,"A"),Cells(16,"E")) or Range(Cells(2,1),Cells(16,5)). I will update the code in the post to support your comment about merging columns A to C. If this has worked for you, please ensure you hit the checkmark to accept the answer so this can be marked as Answered. – Cyril Feb 18 '19 at 19:04
I tried the new code and I cannot get it to work. I have string values in columns A through C and I am trying to merge and center them. Below is a example of what my Column A looks like. – Chris Feb 18 '19 at 19:28
red red red blue blue red red green green -Vertically – Chris Feb 18 '19 at 19:28
@Chris I just went and added the extra closing parenthsis for Range() around the cell references and swapped .merge to be .mergecells=true. Note that you would need to update your post to show any images or code, as these comments are not sufficient. – Cyril Feb 19 '19 at 15:00

How do I merge cells in another worksheet?

1 Answers1