Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
select rows and delete based on criteria
hi sorry for being a pain...
I have a worksheet with data from column A to E and from rows 1 to (lets say) 5000. I want a macro that serches for rows that have "2" in column C and are NOT BLANK, then select all thses rows from column A to IV (not just A to E) and then delete them (shiftng all other rows up but i think this happens automatically. How can I do this. Also in the resulting table I want to select all remaianing rows (again from A to IV). thank you. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
select rows and delete based on criteria
Hi Steven
Start here http://www.rondebruin.nl/delete.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "steven" wrote in message ... hi sorry for being a pain... I have a worksheet with data from column A to E and from rows 1 to (lets say) 5000. I want a macro that serches for rows that have "2" in column C and are NOT BLANK, then select all thses rows from column A to IV (not just A to E) and then delete them (shiftng all other rows up but i think this happens automatically. How can I do this. Also in the resulting table I want to select all remaianing rows (again from A to IV). thank you. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
select rows and delete based on criteria
this is what i made out of the webpage
Sub deleterows2crtr() ElseIf .Cells(Lrow, "E").Value = "2" And _ .Cells(Lrow, "G").Value < 0 Then .Rows(Lrow).Delete End Sub it dosent seem to work though. Invalid criteria or something. Can you help out? S. "Ron de Bruin" wrote: Hi Steven Start here http://www.rondebruin.nl/delete.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "steven" wrote in message ... hi sorry for being a pain... I have a worksheet with data from column A to E and from rows 1 to (lets say) 5000. I want a macro that serches for rows that have "2" in column C and are NOT BLANK, then select all thses rows from column A to IV (not just A to E) and then delete them (shiftng all other rows up but i think this happens automatically. How can I do this. Also in the resulting table I want to select all remaianing rows (again from A to IV). thank you. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
select rows and delete based on criteria
Hi Steven
Try this Sub Example2() Dim Lrow As Long Dim CalcMode As Long Dim ViewMode As Long Dim StartRow As Long Dim EndRow As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView With ActiveSheet .DisplayPageBreaks = False StartRow = 1 EndRow = 5000 For Lrow = EndRow To StartRow Step -1 If .Cells(Lrow, "E").Value = 2 And _ .Cells(Lrow, "G").Value < 0 Then .Rows(Lrow).Delete Next End With ActiveWindow.View = ViewMode With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "steven" wrote in message ... this is what i made out of the webpage Sub deleterows2crtr() ElseIf .Cells(Lrow, "E").Value = "2" And _ .Cells(Lrow, "G").Value < 0 Then .Rows(Lrow).Delete End Sub it dosent seem to work though. Invalid criteria or something. Can you help out? S. "Ron de Bruin" wrote: Hi Steven Start here http://www.rondebruin.nl/delete.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "steven" wrote in message ... hi sorry for being a pain... I have a worksheet with data from column A to E and from rows 1 to (lets say) 5000. I want a macro that serches for rows that have "2" in column C and are NOT BLANK, then select all thses rows from column A to IV (not just A to E) and then delete them (shiftng all other rows up but i think this happens automatically. How can I do this. Also in the resulting table I want to select all remaianing rows (again from A to IV). thank you. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
select rows and delete based on criteria
niewbe me... never mind I figured t out!
thank you! "Ron de Bruin" wrote: Hi Steven Start here http://www.rondebruin.nl/delete.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "steven" wrote in message ... hi sorry for being a pain... I have a worksheet with data from column A to E and from rows 1 to (lets say) 5000. I want a macro that serches for rows that have "2" in column C and are NOT BLANK, then select all thses rows from column A to IV (not just A to E) and then delete them (shiftng all other rows up but i think this happens automatically. How can I do this. Also in the resulting table I want to select all remaianing rows (again from A to IV). thank you. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete rows based on multiple criteria | Excel Discussion (Misc queries) | |||
How can I delete rows programmatically based on certain criteria? | New Users to Excel | |||
Delete rows based on criteria | Excel Discussion (Misc queries) | |||
Delete rows based on certain criteria | Excel Discussion (Misc queries) | |||
Select rows based on criteria | Excel Worksheet Functions |