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

teezarromy

New Member
Joined
Jun 24, 2009
Messages
4
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
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

teezarromy

New Member
Joined
Jun 24, 2009
Messages
4
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

New Member
Joined
Jun 24, 2009
Messages
4
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Try putting:

Application.DisplayAlerts = False

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

Watch MrExcel Video

Forum statistics

Threads
1,109,451
Messages
5,528,832
Members
409,839
Latest member
akashsadhu
Top