Range Help

M

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
 
S

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:
 
M

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?
 
S

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:
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top