Select a range with merged calls in first column

Question

I have some code that is working fine, but it is not selecting and pasting the last row of data.

Column AL has a value in every other row e.g. rows 1,3,5 (AL1:AL2,AL3:AL4, AL5:AL6 are merged cells). The other columns are not merged and have values in rows 1-6). When I run the VBA code, row 6 is not being included (all other data is being pasted correctly).

I'm trying to select the cells range and then offset by 1 row (to try and pick up row 6), but that does not seem to be working. I can't find a solution.

Dim lr As Long
Dim drng As Range 'dest range
Dim srng As Range 'source range
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
            lr = ws.Range("A1").SpecialCells(xlCellTypeLastCell).Row
            Set srng = ws.Range("AL1:AP" & lr)
            Set drng = SumSh.Range("B" & Rows.Count).End(xlUp)(2)
            Set drng = drng.Resize(srng.Rows.Count, srng.Columns.Count)
            drng.Value = srng.Value
        Next ws

What is ws.Range("AL1:AP69" & lr) intended to represent? Have you run a debug.print on the cell range address that is trying to reference? — user4039065
It should say ws.Range("AL1:AP" & lr). It is a table of numbers. Column AL has merged cells every other row (i.e. every other row has data). The other columns have data in every row. — Seale

Unknown Unknown · Accepted Answer · 2015-08-09T02:51:55

After correcting your example code to ws.Range("AL1:AP" & lr) it ran without incident or missing any rows. The target cells in the first column were not merged but all rows were there.

Sub gettit()
    Dim lr As Long
    Dim drng As Range 'dest range
    Dim srng As Range 'source range
    Dim ws As Worksheet, SumSh As Worksheet

    Set SumSh = Worksheets("Sum")
    For Each ws In ActiveWorkbook.Worksheets
        With ws
            If .Name = "Sum" Then Exit For
            lr = .Range("A1").SpecialCells(xlCellTypeLastCell).Row
            Set srng = .Range("AL1:AP" & lr)
            Set drng = SumSh.Range("B" & Rows.Count).End(xlUp)(2)
            Set drng = drng.Resize(srng.Rows.Count, srng.Columns.Count)
            drng.Value = srng.Value
        End With
    Next ws
End Sub

I did have to set the target worksheet as that had been left undeclared and unassigned.

I suspect your loop is not picking up the next blank row of the target worksheet because the cells in column B are not merged. In other words, it is overwriting the last row with incoming data because you are asking for the next blank cell in column B and column B always has a blank cell starting the last row.

The last iteration of the loop should be correct; albeit with a blank cell in the last row of column B. Solution: move down an extra row on the target.

Select a range with merged calls in first column

1 Answers