Range Help

[Miree]

I have this section of code, can I modify this to look at a range of
columns(AN - BF) and only delete line if it does not appear any of the cells
in row within tose columns?

Dim rng As Range
Dim i As Long

Set rng = ActiveSheet.Range(Cells(1, "?"), Cells(Rows.Count, "?").End(xlUp))

With rng
For i = .Rows.Count To 1 Step -1
If Not .Cells(i) = UserForm7.ComboBox5.Value Then
.Cells(i).EntireRow.Delete
End If
Next i
End With

Any help/advice is much appreciated

 

[Stefi]

Set rng = ActiveSheet.Range(Cells(1, "AN"), Cells(Rows.Count, "BF").End(xlUp))
works, but I couldn't understand "if it does not appear any of the cells in
row within tose columns". Please explain it in more detail!

Regards,
Stefi


„Miree†ezt írta:

 

[Miree]

Curenty the code looks looks up column AN, if combobox 5 is not matched in
the cell then the row is deleted. Now I need it to search columns AN to BF,
combobox 5 should only appear in once of these cells, if it doesnt appear in
any cells between AN and BF i want the row deleted, does this make better
sense?

 

[Stefi]

Now it's clear. Try this:

Sub test()
Dim rng As Range
Dim i As Long
Dim delrow As Boolean

'cbval = "CB"

Set rng = ActiveSheet.Range(Cells(1, "AN"), Cells(Rows.Count, "BF").End(xlUp))
rng.Select

With rng

For i = .Rows.Count To 1 Step -1
delrow = True

For j = 1 To .Columns.Count
If Not .Cells(i, j) = UserForm7.ComboBox5.Value Then
' If .Cells(i, j) = cbval Then
delrow = False
Exit For
End If
Next j
If delrow Then .Cells(i, 1).EntireRow.Delete
Next i
End With

End Sub

Regards,
Stefi

„Miree†ezt írta:

 

[Miree]

after a little modification it works beautifully thank you so much

 

[Stefi]

You are welcome! Thanks for the feedback!
Stefi

„Miree†ezt írta: