Complicated code

M

Miree

I have the following code which works beautifully, I now need to modify or
write a new code to do the next step.

At the moment the code deletes the row if combobox5 does not appear in a
cell between AN and BF what I now need to do is If combobox5 does appear in
a cell I need to check that the cell 38 along (BZ to CR) has a number which
is between textbox1 and textbox2 if not delete the row

Dim rng As Range
Dim i As Long
Dim delrow As Boolean

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 .Cells(i, j) = UserForm7.ComboBox5.Value Then
delrow = False
End If
Next j

If delrow = True Then .Cells(i, 1).EntireRow.Delete
Next i
End With

Any help much appreciated
 
J

joel

I think you are look at the data in Column DK. I re-wrote the code so it is
easier to see which coluns you are referening to. You had one error in your
code where you weren't checking the activesheet

Cells(Rows.Count, "BF") - missing the period.
Cells(1, "AN") - missing period.

this error probably wouldn't of been a problem in this code but can cause
problems when you aren't working with the activeworksheet.

Sub test()

Dim delrow As Boolean

With ActiveSheet
LastRow = .Cells(Rows.Count, "BF").End(xlUp).Row
FirstColumn = .Range("AN1").Column
Lastcolumn = .Range("BF1").Column

For RowCount = LastRow To 1 Step -1
For ColCount = FirstColumn To Lastcolumn


delrow = True

If .Cells(RowCount, ColCount) = UserForm7.ComboBox5.Value Then
delrow = False
End If
Next ColCount

If delrow = True Then
DataValue = .Range("DK" & RowCount).Value
If Val(UserForm7.Textbox1.Value) >= DataValue And _
Val(UserForm7.Textbox2.Value) >= DataValue Then

.Rows(RowCount).Delete
End If
End If
Next RowCount

End With


End Sub
 

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

Similar Threads

Range Help 5
Match style 0
delete duplicate records 2
Combobox date value matching 0
ComboBox value matching 0
VBA Export to PDF 0
Remove Identical words 0
Modify working code to expand the output. 5

Top