How can I prevent a merged cell, in a Do While loop, from moving my selected cell from one column to another in VBA?

Question

I receive excel files from a bank that contains information that I need in a certain column. So I want to loop through the specific column and get the values of those cells. Lets say I select column B. I start at B1 and loop through the column cells. But once I get to a merged cell, which there are quite a lot of, the merged cell throws me off of column B when I try to move past it. I'm using Offset(1, 0) to go down the column.

'Here is a quick example of how the selected cell will move
'I'm using an index to move down 15 cells
'Merge cell A2 and B2 before running the macro

Sub test()

Dim index As Integer
index = 0

Range("B1").Select

Do While index < 15

    Selection.Offset(1, 0).Select
    index = index + 1

Loop

End Sub

The selection moves from B1 to B2, which is merged with A2, then continue to A3 instead of B3.

What's your ultimate aim here, using VBA to select cells and then copy them or do something with the values in them? — mjsqu, Aug 22 '19 at 22:21
If you don't select the cells you can resolve a lot of potential problems... — Tim Williams, Aug 22 '19 at 22:22
Loops + Selection.Offset = instant headache. Use `Cells(row, column)` instead. — Mathieu Guindon, Aug 22 '19 at 22:24
@mjsqu Most of the cells in workbook A will be empty, except for the merged cells and the cells that contain the values I need. So I need to move down the column of workbook A, get the value of the cells I need, compare the cell with cells in a column in workbook B. If the cells are equal, I need to copy certain cells in the same row of the cell in workbook B, and paste them in workbook A near the original cell of workbook A. After that I need to move down the column again and repeat the whole process. — Dian van Schalkwyk, Aug 23 '19 at 13:19

Vityata · Answer 1 · 2019-08-22T22:32:41.407

Using Select is not a best practice, but if you need it for some visual reasons, the code below would work. It gets a starting cell startingRange and each time it loops one row down from it - startingRange.Offset(rowoffset:=index).Select

Sub TestMe()

    Dim index As Long
    index = 0

    Dim startingRange As Range
    Set startingRange = Worksheets(1).Range("B1")

    Do While index < 15
        startingRange.Offset(rowoffset:=index).Select
        Application.Wait (Now + #12:00:01 AM#)
        index = index + 1
    Loop

End Sub

The Application.Wait (Now + #12:00:01 AM#) is added in order to visualize the Seleced cell better.

score 0 · Answer 2 · answered Aug 26 '19 at 01:12

You should do this without using Select, VBA can address any cell in your workbook without selecting it first and find out what value it contains. There are doubtless different ways of doing what you want to achieve, but this example explains how to move through a column without it deviating:

Sub test()
For Each Cell In Range("B1:B14").Cells
    'Finds the MergeArea of the Cell and gets the value from the top left cell
    MsgBox Cell.MergeArea.Cells(1, 1).Value
Next
End Sub

When you run the sub it'll fire off Message Boxes containing the values of the cells in column B, rows 1-14, including those that are merged.

This is probably an incomplete answer, your final answer may be a mixture of the code in this example plus the code in your original question.

How can I prevent a merged cell, in a Do While loop, from moving my selected cell from one column to another in VBA?

2 Answers2