Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 -- leskoby ------------------------------------------------------------------------ leskoby's Profile: http://www.excelforum.com/member.php...o&userid=26616 View this thread: http://www.excelforum.com/showthread...hreadid=398939 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Rows using a range in Column A
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 :) leskoby Wrote: I regularly receive a file of financial data and it contains 4 digi cost center numbers in column A. There are cost center numbers in whic I am not interested and would like to use a macro to delete the row 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 yo want to evaluate. For counter = 1 To rng.Rows.Count 'If cell i in the range contains a cost center number betwee 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 Nex -- T-Že ----------------------------------------------------------------------- T-Žex's Profile: http://www.excelforum.com/member.php...fo&userid=2657 View this thread: http://www.excelforum.com/showthread.php?threadid=39893 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Rows using a range in Column A
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Rows using a range in Column A
Thanks, both sets of code worked. -- leskoby ------------------------------------------------------------------------ leskoby's Profile: http://www.excelforum.com/member.php...o&userid=26616 View this thread: http://www.excelforum.com/showthread...hreadid=398939 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting rows in a range using Autofilter | Excel Discussion (Misc queries) | |||
Deleting rows with no value in column A | Excel Programming | |||
Define Range in Deleting Empty Rows | Excel Programming | |||
deleting rows in a non-continous range | Excel Programming | |||
deleting rows in a non-continous range | Excel Programming |