Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2000 VBA - Delete Rows That Meet Criteria
I am downloading to Excel a report from a financial system. The reports
length (# of rows) will vary. I want to delete the entire rows in a range called Company_Code (a range for rows in column "A" up to the last row that has data) that are either "Blank" or do NOT contain the values "Cred", "PTUB", "CMEX". Can somebody please provide me with the VBA code to do this? I've played around with this forever and can't get it to work. Also, I don't know if I am doing it the most efficient way. Any help would be MUCH appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2000 VBA - Delete Rows That Meet Criteria
You don't need VBA. Just do a filter (DataFilterAutofilter) and select a
custom value of Blank and not Cred, and then just delete the visible rows. -- HTH Bob Phillips "William Horton" <William wrote in message ... I am downloading to Excel a report from a financial system. The reports length (# of rows) will vary. I want to delete the entire rows in a range called Company_Code (a range for rows in column "A" up to the last row that has data) that are either "Blank" or do NOT contain the values "Cred", "PTUB", "CMEX". Can somebody please provide me with the VBA code to do this? I've played around with this forever and can't get it to work. Also, I don't know if I am doing it the most efficient way. Any help would be MUCH appreciated. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2000 VBA - Delete Rows That Meet Criteria
Try this macro
Sub Example1() Dim Firstrow As Long Dim Lastrow As Long Dim Lrow As Long Dim CalcMode As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With Firstrow = ActiveSheet.UsedRange.Cells(1).Row Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1 With ActiveSheet .DisplayPageBreaks = False For Lrow = Lastrow To Firstrow Step -1 If IsError(.Cells(Lrow, "A").Value) Then 'Do nothing, This avoid a error if there is a error in the cell ElseIf .Cells(Lrow, "A").Value = "" And _ .Cells(Lrow, "A").Value < "Cred" And _ .Cells(Lrow, "A").Value < "PTUB" And _ .Cells(Lrow, "A").Value < "CMEX" Then .Rows(Lrow).Delete End If Next End With With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "William Horton" <William wrote in message ... I am downloading to Excel a report from a financial system. The reports length (# of rows) will vary. I want to delete the entire rows in a range called Company_Code (a range for rows in column "A" up to the last row that has data) that are either "Blank" or do NOT contain the values "Cred", "PTUB", "CMEX". Can somebody please provide me with the VBA code to do this? I've played around with this forever and can't get it to work. Also, I don't know if I am doing it the most efficient way. Any help would be MUCH appreciated. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2000 VBA - Delete Rows That Meet Criteria
Oops
ElseIf .Cells(Lrow, "A").Value = "" Or _ .Cells(Lrow, "A").Value < "Cred" And _ .Cells(Lrow, "A").Value < "PTUB" And _ .Cells(Lrow, "A").Value < "CMEX" Then .Rows(Lrow).Delete -- Regards Ron de Bruin http://www.rondebruin.nl "Ron de Bruin" wrote in message ... Try this macro Sub Example1() Dim Firstrow As Long Dim Lastrow As Long Dim Lrow As Long Dim CalcMode As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With Firstrow = ActiveSheet.UsedRange.Cells(1).Row Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1 With ActiveSheet .DisplayPageBreaks = False For Lrow = Lastrow To Firstrow Step -1 If IsError(.Cells(Lrow, "A").Value) Then 'Do nothing, This avoid a error if there is a error in the cell ElseIf .Cells(Lrow, "A").Value = "" And _ .Cells(Lrow, "A").Value < "Cred" And _ .Cells(Lrow, "A").Value < "PTUB" And _ .Cells(Lrow, "A").Value < "CMEX" Then .Rows(Lrow).Delete End If Next End With With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "William Horton" <William wrote in message ... I am downloading to Excel a report from a financial system. The reports length (# of rows) will vary. I want to delete the entire rows in a range called Company_Code (a range for rows in column "A" up to the last row that has data) that are either "Blank" or do NOT contain the values "Cred", "PTUB", "CMEX". Can somebody please provide me with the VBA code to do this? I've played around with this forever and can't get it to work. Also, I don't know if I am doing it the most efficient way. Any help would be MUCH appreciated. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2000 VBA - Delete Rows That Meet Criteria
THANKS RON! Your code did exactly what I needed and was very much appreciated.
"Ron de Bruin" wrote: Oops ElseIf .Cells(Lrow, "A").Value = "" Or _ .Cells(Lrow, "A").Value < "Cred" And _ .Cells(Lrow, "A").Value < "PTUB" And _ .Cells(Lrow, "A").Value < "CMEX" Then .Rows(Lrow).Delete -- Regards Ron de Bruin http://www.rondebruin.nl "Ron de Bruin" wrote in message ... Try this macro Sub Example1() Dim Firstrow As Long Dim Lastrow As Long Dim Lrow As Long Dim CalcMode As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With Firstrow = ActiveSheet.UsedRange.Cells(1).Row Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1 With ActiveSheet .DisplayPageBreaks = False For Lrow = Lastrow To Firstrow Step -1 If IsError(.Cells(Lrow, "A").Value) Then 'Do nothing, This avoid a error if there is a error in the cell ElseIf .Cells(Lrow, "A").Value = "" And _ .Cells(Lrow, "A").Value < "Cred" And _ .Cells(Lrow, "A").Value < "PTUB" And _ .Cells(Lrow, "A").Value < "CMEX" Then .Rows(Lrow).Delete End If Next End With With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "William Horton" <William wrote in message ... I am downloading to Excel a report from a financial system. The reports length (# of rows) will vary. I want to delete the entire rows in a range called Company_Code (a range for rows in column "A" up to the last row that has data) that are either "Blank" or do NOT contain the values "Cred", "PTUB", "CMEX". Can somebody please provide me with the VBA code to do this? I've played around with this forever and can't get it to work. Also, I don't know if I am doing it the most efficient way. Any help would be MUCH appreciated. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2000 VBA - Delete Rows That Meet Criteria
Thanks for the feedback
Other examples you can find here http://www.rondebruin.nl/delete.htm -- Regards Ron de Bruin http://www.rondebruin.nl "William Horton" wrote in message ... THANKS RON! Your code did exactly what I needed and was very much appreciated. "Ron de Bruin" wrote: Oops ElseIf .Cells(Lrow, "A").Value = "" Or _ .Cells(Lrow, "A").Value < "Cred" And _ .Cells(Lrow, "A").Value < "PTUB" And _ .Cells(Lrow, "A").Value < "CMEX" Then .Rows(Lrow).Delete -- Regards Ron de Bruin http://www.rondebruin.nl "Ron de Bruin" wrote in message ... Try this macro Sub Example1() Dim Firstrow As Long Dim Lastrow As Long Dim Lrow As Long Dim CalcMode As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With Firstrow = ActiveSheet.UsedRange.Cells(1).Row Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1 With ActiveSheet .DisplayPageBreaks = False For Lrow = Lastrow To Firstrow Step -1 If IsError(.Cells(Lrow, "A").Value) Then 'Do nothing, This avoid a error if there is a error in the cell ElseIf .Cells(Lrow, "A").Value = "" And _ .Cells(Lrow, "A").Value < "Cred" And _ .Cells(Lrow, "A").Value < "PTUB" And _ .Cells(Lrow, "A").Value < "CMEX" Then .Rows(Lrow).Delete End If Next End With With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "William Horton" <William wrote in message ... I am downloading to Excel a report from a financial system. The reports length (# of rows) will vary. I want to delete the entire rows in a range called Company_Code (a range for rows in column "A" up to the last row that has data) that are either "Blank" or do NOT contain the values "Cred", "PTUB", "CMEX". Can somebody please provide me with the VBA code to do this? I've played around with this forever and can't get it to work. Also, I don't know if I am doing it the most efficient way. Any help would be MUCH appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel formular to Count values in cells of rows that meet criteria | Excel Worksheet Functions | |||
How to copy rows that meet criteria to another sheet in Excel | Excel Worksheet Functions | |||
Formula/Macro to delete rows that do not meet criteria from a list? | Excel Worksheet Functions | |||
Hide rows that meet certain criteria in an excel template | Excel Worksheet Functions | |||
Delete Rows where cells does not meet criteria | Excel Worksheet Functions |