delete row based cell value

[brundag5]

Hello,

I have a spreadsheet with data in column A. Rows 1 to 16 are filled with header information and a linest function. Since the linest function does not exclude hidden cells I need a macro that will delete the row if the cell value is less than my input criteria from F1 and also if the value is greater than the value in F2.

I have tried the below code (butchered from another site) but 1 it is slow (not really a problem) and 2 on the second pass it deletes the data in rows 1 to 16. If anyone can help with this task I would very much appreciate it.

Thanks,
Craig

Sub del()
    ' Turn off screen updating.3
Application.ScreenUpdating = False
Dim LR As Long, i As Long, j As Long, LR2 As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
    If Range("A" & i).Value <= Range("F1") Then Rows(i).EntireRow.Delete
    Next i
LR2 = Range("A" & Rows.Count).End(xlUp).Row
For j = LR2 To 1 Step -1
    If Range("A" & j).Value >= Range("F2") Then Rows(j).EntireRow.Delete
Next j
End Sub

 

[JoeMo]

Your current code looks at col A cells from A1 to the last filled cell in col A, and it deletes entire rows if they meet the criteria in cells F1 or F2. This risks deleting the criteria cells. Are you ok with just deleting Col A cells rather than the entire rows for those cells?

 

[brundag5]

I need to delete the entire row because other columns contain data used in the linest calculation. Thanks for your reply.

 

[JoeMo]

Do those other columns include col F? If yes, then do cells A1 and A2 contain numbers or do they hold text? If text, the code can be written to avoid deleting your criteria cells.

 

[brundag5]

After a more research I discovered that that I could specify the start row for the cod (in this case row 3). This seems to work but runs pretty slow. Maybe this is normal? Can this be cleaned up or is this the best way to do it?
@JoeMo No, column F is only holds the criteria for deletion. A1 and A2 is a gap created so that I don't delete the information in F1 / F2.

Sub del()
    ' Turn off screen updating.3
Application.ScreenUpdating = False
Dim LR As Long, i As Long, j As Long, LR2 As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 3 Step -1
    If Range("A" & i).Value <= Range("F1") Then Rows(i).EntireRow.Delete
    Next i
LR2 = Range("A" & Rows.Count).End(xlUp).Row
For j = LR2 To 3 Step -1
    If Range("A" & j).Value >= Range("F2") Then Rows(j).EntireRow.Delete
Next j
End Sub