How to loop a range in for-next statement using the VBA?

[teezarromy]

Hello,

Basically, I have 5 different columns in my table (Firstname, lastname, data1, data 2 and data3. If the firstname and lastname are duplicated, I want to merge the corresponging cells. However, I have problem with the syntax of the range in the for-next statement. Can Anyone help? Below is my code.


Sub CondenseTable()
Dim i As Integer
i = 2
For i = 2 To 8
If Worksheets("Sheet1").Range("A" & i).Value = Worksheets("Sheet1").Range("A" & i + 1) And _
Worksheets("Sheet1").Range("B" & i).Value = Worksheets("Sheet1").Range("B" & i + 1) Then

Range("A" & i:"A" & i+1).Select
Selection.Merge

Range("B" & i:"B" & i+1).Select
Selection.Merge

Range("C" & i:"C" & i+1).Select
Selection.Merge

Range("D" & i:"D" & i+1).Select
Selection.Merge

Range("E" & i:"E" & i+1).Select
Selection.Merge

End If

Next i
End Sub

Thanks,
Teezar

 

[Andrew Poulsom]

Welcome to the Board.

The syntax is:

Range("A" & i & ":A" & i + 1).Select

 

[teezarromy]

Andrew,

Thanks for your help and warm welcome. The syntax works. By the way, I keep getting a message box saying that only the left data will be saved if I merged the columns. How can I write a macro to automate the clicking the "ok" button?

Thanks,
Teezar

 

[teezarromy]

Allow me to make my problem clear. When merging two cells with different data, I get the message box saying "The selection contains multiple data value". Merging into one cell will only kep the most upper-left data only" To allow the merge, I have to click ok.

I hope someone can help me with the macro to automate the clicking "OK" button.

Thnks
Tzar

 

[Andrew Poulsom]

Try putting:

Application.DisplayAlerts = False

at the beginning of your code. Set it back to True at the end.

 

[teezarromy]

Thanks. It works.