Deleting entire rows based on criteria

[suhi100]

Hi,

I have an excel sheet with hunderds of rows but I only need to keep tho rows in which the value in column A is smaller than 8000.
I have found a code ,which deletes entire rows if they are empty, I guess it can be altered somehow to delete entire rows if the value in column A in that row is smaller than 8000.

Here is the code:

'Step1 Declare your variables

Dim myRange As range
Dim icounter As Long

'Step2 Define the target range

Set myRange = ActiveSheet.usedrange

'Step3 Start reverse looping through the range

For icounter = myRange.Rows.count To 1 Step -1

'Step4 If entire column empty delete it.

If Application.count(Rows(icounter).entirerow) = 0 Then
Rows(icounter).Delete
End If

'step5 Increment the counter down

Next icounter
End Sub


Hope you can help with this,thanks!

 

[Oeldere]

Try this one

Sub DeleteValueWith_0_in_column_A()
Dim r As Long
For r = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
If Cells(r, 1) = <8000 Then Rows(r).Delete
Next r
End Sub

 

[suhi100]

Great! It works thanks!
Suhi100

 

[Oeldere]

Thanks for the reply Glad I could help.

 

[suhi100]

Hello again,

Just testing the code you have sent and is working great. There is only one thing that needs to be added to it because this macro not only deletes all the rows that satisfies the criteria Cells(r, 1) = <8000 Then Rows(r).Delete, but it deletes rows in which cells(r,1) contain text. So for example in cell A3 I have 8001 and in A2 I have the text TOTAL. The macro would delete both rows,but iI would like to keep the row in which the first column is text. Also, all the cells where the value is TEXT are actually merged cells and have different color than the cells with numbers. I was trying to add more if statements to the code but did not work...

Any ideas?

Cheers,

Suhi