Error: Macro to delete rows wit a certain value - VBA - Forum Excel, VBA, VSTO, Exceltips, Excelhjälp

Sök Forumets startsida

Forum

Diskussioner

VBA

Error: Macro to delete rows wit a certain value

Föregående

Nästa

2013-08-16 13:35

cjwe

Medlem sedan: 2013-08-16

1 inlägg

Error: Macro to delete rows wit a certain value

I want to delete rows with the value "unique" in column c. For that i have written the following macro. However when i run the macro it deletes a majority of the rows but not all. If i run it a second time it deletes some more but i have to run it a couple of times for it to delete all of them. Could someone please explain why or how to modify the macro to work properly.

Thank you.

Sub DeleteUnique()

Dim Rng2 As Range
Dim SecRng As Range

Set SecRng = Range(Range("c2"), Range("c" & Rows.Count).End(xlUp))
For Each Rng2 In SecRng
If Rng2.Value = "unique" Then
Rng2.EntireRow.Delete
End If
On Error Resume Next
Next Rng2

End Sub

2013-09-19 07:37

Alf

Medlem sedan: 2013-09-19

6 inlägg

Re: Error: Macro to delete rows wit a certain value

You problem is caused by deleting rows top to bottom. Try the other way round instead i.e.

Sub DelUnique()

Dim i As Integer

For i = Range("C" & Rows.Count).End(xlUp).Row To 2 Step -1

If Cells(i, 3) = "unique" Then

Rows(i).EntireRow.Delete

End If

End Sub

Depending on how much data you have another option would be to use "Autofilter" with "unique" as criteria and then delete all the rows that matches in one go.

Alf

2013-09-19 07:37

Alf

Medlem sedan: 2013-09-19

6 inlägg

Re: Error: Macro to delete rows wit a certain value

Deleted as this was a duplicate message

Sida 1 av 1

Error: Macro to delete rows wit a certain value