Deleting Rows using a range in Column A

I regularly receive a file of financial data and it contains 4 digit cost center numbers in column A. There are cost center numbers in which I am not interested and would like to use a macro to delete the rows which contain the numbers in column A in the range 5600 to 6500.

The code I'm using loops so I would appreciate any help.

'Start Test Conditional Delete
Range("A1:A1").Select

Dim rng As Range
Dim i As Integer, counter As Integer

'Set the range to evaluate to rng.

'initialize i to 1
i = 1

'Loop for a count of 1 to the number of rows in the range that you want to evaluate.
For counter = 1 To rng.Rows.Count

'If cell i in the range contains a cost center number between 5600 and 6500
delete the row.

If rng.Cells(i) < 6500 Or rng.Cells(i) > 5599 Then
rng.Cells(i).EntireRow.Delete
Else
i = i + 1
End If
Next

your code works if you change

If rng.Cells(i) < 6500 Or rng.Cells(i) > 5599

to

If rng.Cells(i) < 6500 And rng.Cells(i) > 5599

Originally Posted by leskoby

I regularly receive a file of financial data and it contains 4 digit cost center numbers in column A. There are cost center numbers in which I am not interested and would like to use a macro to delete the rows which contain the numbers in column A in the range 5600 to 6500.

The code I'm using loops so I would appreciate any help.

'Start Test Conditional Delete
Range("A1:A1").Select

Dim rng As Range
Dim i As Integer, counter As Integer

'Set the range to evaluate to rng.

'initialize i to 1
i = 1

'Loop for a count of 1 to the number of rows in the range that you want to evaluate.
For counter = 1 To rng.Rows.Count

'If cell i in the range contains a cost center number between 5600 and 6500
delete the row.

If rng.Cells(i) < 6500 Or rng.Cells(i) > 5599 Then
rng.Cells(i).EntireRow.Delete
Else
i = i + 1
End If
Next

Dim i As Long
Dim iLastRow As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 1 Step -1
If Cells(i, "A").Value < 6500 And Cells(i, "A").Value > 5599 Then
Rows(i).Delete
End If
Next i


--

HTH

RP
(remove nothere from the email address if mailing direct)


"leskoby" <leskoby.1ub8ub_1124957123.5665@excelforum-nospam.com> wrote in
message news:leskoby.1ub8ub_1124957123.5665@excelforum-nospam.com...
>
> I regularly receive a file of financial data and it contains 4 digit
> cost center numbers in column A. There are cost center numbers in which
> I am not interested and would like to use a macro to delete the rows
> which contain the numbers in column A in the range 5600 to 6500.
>
> The code I'm using loops so I would appreciate any help.
>
> 'Start Test Conditional Delete
> Range("A1:A1").Select
>
> Dim rng As Range
> Dim i As Integer, counter As Integer
>
> 'Set the range to evaluate to rng.
>
> 'initialize i to 1
> i = 1
>
> 'Loop for a count of 1 to the number of rows in the range that you
> want to evaluate.
> For counter = 1 To rng.Rows.Count
>
> 'If cell i in the range contains a cost center number between
> 5600 and 6500
> delete the row.
>
> If rng.Cells(i) < 6500 Or rng.Cells(i) > 5599 Then
> rng.Cells(i).EntireRow.Delete
> Else
> i = i + 1
> End If
> Next
>
>
> --
> leskoby
> ------------------------------------------------------------------------
> leskoby's Profile:
http://www.excelforum.com/member.php...o&userid=26616
> View this thread: http://www.excelforum.com/showthread...hreadid=398939
>

Thanks, both sets of code worked.