deleting rows

[paulsolar]

Hi

I'm having a bad day today

I want to delete rows where column V contains a variable text

I normally use
For i = Selection.Rows.Count To 1 Step -1
If Cells(i, 22).Value = "???????" Then ' the question marks are what I want to remove
Cells(i, 22).EntireRow.Delete
End If
Next i

What I actually want to do is remove any rows where the data in column V starts with HS-

cheers

Paul

 

[Peter_SSs]

How big is your data?

BTW, you still have not updated your version details.

 

[paulsolar]

How big is your data?

BTW, you still have not updated your version details.

Many thanks for your reply, 3 or 400 rows, its not massive. Where do i update my version data please?

 

[Peter_SSs]

Where do i update my version data please?

As I advised here.

3 or 400 rows

OK, then try this

Sub DelRws()
  Dim i As Long
 
  Application.ScreenUpdating = False
    For i = Range("V" & Rows.Count).End(xlUp).Row To 2 Step -1
      If Cells(i, "V").Value Like "HS-*" Then Rows(i).Delete
    Next i
  Application.ScreenUpdating = True
End Sub

 

[paulsolar]

As I advised here.



OK, then try this

Sub DelRws()
  Dim i As Long
 
  Application.ScreenUpdating = False
    For i = Range("V" & Rows.Count).End(xlUp).Row To 2 Step -1
      If Cells(i, "V").Value Like "HS-*" Then Rows(i).Delete
    Next i
  Application.ScreenUpdating = True
End Sub

it doesnt appear to be working,

 

[Peter_SSs]

Thanks for updating your details.

it doesnt appear to be working,

That gives us nothing to go on.
Did it ..

• Crash Excel?
• Give an error message? (What message on what line?)
• Delete the wrong rows? (Details)
• Do nothing?
• Something else?

Given that it worked for what I understood your data was like, it is likely your data and my sample data are different in some way.
Here is mine before the code was run

paulsolar.xlsm
	V
1	Heading
2	abc
3	HS-sad
4	xyz
5	HS-vsdf
6	HS-
7	HS-HS-
8	HSV
9	zzz
10
11	yyy
Sheet1

After

paulsolar.xlsm
	V
1	Heading
2	abc
3	xyz
4	HSV
5	zzz
6
7	yyy
8
Sheet1

Do I have inappropriate sample data?
Perhaps you could give us a similarly small set of representative sample of your data (any sensitive information disguised or removed) with XL2BB so that we can easily test with appropriate data?

 

[Skyybot]

Sorry if I'm off here and don't understand the issue, but give this a shot. It's a small change to your original code.

For i = Selection.Rows.Count To 1 Step -1
If Left(Cells(i, 22).Value,3) = "HS-" Then ' the question marks are what I want to remove
Cells(i, 22).EntireRow.Delete
End If
Next i