I have the following data in an excel sheet
A B C D E F G
unique member_id name phone email-id address date
I have the formula "IF(B2=B3,0,1)" in cell A2 and copied down column A.
I have duplicate entries in B, which sets the corresponding "unique" cell as '0'. Suppose cell A5 has unique field as '0';
Step 1: search for cells (like A5) in col A
Step 2: Copy all data in A6:G(last row) and paste it on A5:G5 row, thus removing the duplicate entry in A5:G5.
I have written the following macro to search for cells in col. A with value as '0'; I'm having a hard time writing the copy and paste macro to perform step 2. Please help!
Macro:
Sub NextZero()
Dim LR As Long, i As Long, b As Boolean, count As Long
count = 0
LR = Range("A" & Rows.count).End(xlUp).Row
If ActiveCell.Column <> 1 Then Range("A1").Select
For i = ActiveCell.Row + 1 To LR + 1
With Range("A" & i)
If .Value <> "" And Not .Value = "1" Then
b = True
Application.Run "CopyPaste"
count = count + 1
.Select
Exit For
End If
End With
Next i
If Not b Then MsgBox "No more", vbInformation
A B C D E F G
unique member_id name phone email-id address date
I have the formula "IF(B2=B3,0,1)" in cell A2 and copied down column A.
I have duplicate entries in B, which sets the corresponding "unique" cell as '0'. Suppose cell A5 has unique field as '0';
Step 1: search for cells (like A5) in col A
Step 2: Copy all data in A6:G(last row) and paste it on A5:G5 row, thus removing the duplicate entry in A5:G5.
I have written the following macro to search for cells in col. A with value as '0'; I'm having a hard time writing the copy and paste macro to perform step 2. Please help!
Macro:
Sub NextZero()
Dim LR As Long, i As Long, b As Boolean, count As Long
count = 0
LR = Range("A" & Rows.count).End(xlUp).Row
If ActiveCell.Column <> 1 Then Range("A1").Select
For i = ActiveCell.Row + 1 To LR + 1
With Range("A" & i)
If .Value <> "" And Not .Value = "1" Then
b = True
Application.Run "CopyPaste"
count = count + 1
.Select
Exit For
End If
End With
Next i
If Not b Then MsgBox "No more", vbInformation