kazza101
06-04-2007, 07:49 AM
Hi all
I know this has been posted before, but can't get it to work based on my macro that I have created from looking at others. Am a real newby!!
If the entries in Same_1,Same_2 and Same_3 are all yes, I need the row to be deleted. I have gotten all confused with the counter, which increments the rows down. Plus, also the macro I copied off used offset to show which column value on each row to look at.
Basically, my spreadsheet contains 28 columns, and I want it to look at columns 2,8 and 12, and if all values = yes, to delete the row. Sounds easy but not proving to be!
Application.ScreenUpdating = False
Dim Same_1 As String
Dim Same_2 As String
Dim Same_3 As String
Dim Counter As Integer
Counter = 0
Sheets("CAB Ref Data").Select
Range("a2").Select
Do While ActiveCell.Value <> ""
Counter = Counter + 1
ActiveCell.Offset(1, 0).Activate
Loop
Range("a2").Select
Do While Counter <> 0
ActiveCell.Offset(0, 1).Activate
Same_1 = ActiveCell.Value
'msgbox Same_1
ActiveCell.Offset(0, 6).Activate
Same_2 = ActiveCell.Value
'msgbox Same_2
ActiveCell.Offset(0, 4).Activate
Same_3 = ActiveCell.Value
'Msgbox Same_3
If Same_1 = "yes" And Same_2 = "yes" And Same_3 = "yes" Then
Rows(EntireRow).Select
Selection.Delete Shift:=x1up
Else
Exit Do
End If
ActiveCell.Offset(1, -11).Activate
Counter = Counter - 1
Loop
Range("a1").Select
Application.ScreenUpdating = True
End Sub
I know this has been posted before, but can't get it to work based on my macro that I have created from looking at others. Am a real newby!!
If the entries in Same_1,Same_2 and Same_3 are all yes, I need the row to be deleted. I have gotten all confused with the counter, which increments the rows down. Plus, also the macro I copied off used offset to show which column value on each row to look at.
Basically, my spreadsheet contains 28 columns, and I want it to look at columns 2,8 and 12, and if all values = yes, to delete the row. Sounds easy but not proving to be!
Application.ScreenUpdating = False
Dim Same_1 As String
Dim Same_2 As String
Dim Same_3 As String
Dim Counter As Integer
Counter = 0
Sheets("CAB Ref Data").Select
Range("a2").Select
Do While ActiveCell.Value <> ""
Counter = Counter + 1
ActiveCell.Offset(1, 0).Activate
Loop
Range("a2").Select
Do While Counter <> 0
ActiveCell.Offset(0, 1).Activate
Same_1 = ActiveCell.Value
'msgbox Same_1
ActiveCell.Offset(0, 6).Activate
Same_2 = ActiveCell.Value
'msgbox Same_2
ActiveCell.Offset(0, 4).Activate
Same_3 = ActiveCell.Value
'Msgbox Same_3
If Same_1 = "yes" And Same_2 = "yes" And Same_3 = "yes" Then
Rows(EntireRow).Select
Selection.Delete Shift:=x1up
Else
Exit Do
End If
ActiveCell.Offset(1, -11).Activate
Counter = Counter - 1
Loop
Range("a1").Select
Application.ScreenUpdating = True
End Sub