Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Delte Multiple Rows Given a Value Existing
Ok so i now have this code Option Explicit Sub delete_rows() Dim lastrow As Long Dim row_index As Long Application.ScreenUpdating = False lastrow = ActiveSheet.Cells(Rows.Count, "F").End(xlUp).Row For row_index = lastrow - 1 To 1 Step -1 If Cells(row_index, "F").Value = "Yellow" Then _ Rows(row_index).Delete Next lastrow = ActiveSheet.Cells(Rows.Count, "F").End(xlUp).Row For row_index = lastrow - 1 To 1 Step -1 If Cells(row_index, "F").Value = "Green" Then _ Rows(row_index).Delete Next lastrow = ActiveSheet.Cells(Rows.Count, "F").End(xlUp).Row For row_index = lastrow - 1 To 1 Step -1 If Cells(row_index, "F").Value = "Red" Then _ Rows(row_index).Delete Next lastrow = ActiveSheet.Cells(Rows.Count, "F").End(xlUp).Row For row_index = lastrow - 1 To 1 Step -1 If Cells(row_index, "F").Value = "Blue" Then _ Rows(row_index).Delete Next Application.ScreenUpdating = True End Sub which is very bad programming i know. Could anyone suggest how to tidy it up and also edit it so it runs th cell value checks on every worksheet (could be upto 6) in th workbook. TI -- Jak ----------------------------------------------------------------------- Jako's Profile: http://www.excelforum.com/member.php...nfo&userid=871 View this thread: http://www.excelforum.com/showthread.php?threadid=27169 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Delte Multiple Rows Given a Value Existing
Option Explicit
Sub delete_rows() Dim lastrow As Long Dim row_index As Long Application.ScreenUpdating = False lastrow = ActiveSheet.Cells(Rows.Count, "F").End(xlUp).Row For row_index = lastrow - 1 To 1 Step -1 Select Case LCase(Cells(row_index, "F").Value) Case Is = "yellow", "green", "red", "blue" Rows(row_index).Delete End Select Next row_index Application.ScreenUpdating = True End Sub Why did you use lastrow-1? And I check for yellow, green, red, blue in any case: Red, RED, ReD, etc with that lcase() stuff. Jako wrote: Ok so i now have this code Option Explicit Sub delete_rows() Dim lastrow As Long Dim row_index As Long Application.ScreenUpdating = False lastrow = ActiveSheet.Cells(Rows.Count, "F").End(xlUp).Row For row_index = lastrow - 1 To 1 Step -1 If Cells(row_index, "F").Value = "Yellow" Then _ Rows(row_index).Delete Next lastrow = ActiveSheet.Cells(Rows.Count, "F").End(xlUp).Row For row_index = lastrow - 1 To 1 Step -1 If Cells(row_index, "F").Value = "Green" Then _ Rows(row_index).Delete Next lastrow = ActiveSheet.Cells(Rows.Count, "F").End(xlUp).Row For row_index = lastrow - 1 To 1 Step -1 If Cells(row_index, "F").Value = "Red" Then _ Rows(row_index).Delete Next lastrow = ActiveSheet.Cells(Rows.Count, "F").End(xlUp).Row For row_index = lastrow - 1 To 1 Step -1 If Cells(row_index, "F").Value = "Blue" Then _ Rows(row_index).Delete Next Application.ScreenUpdating = True End Sub which is very bad programming i know. Could anyone suggest how to tidy it up and also edit it so it runs the cell value checks on every worksheet (could be upto 6) in the workbook. TIA -- Jako ------------------------------------------------------------------------ Jako's Profile: http://www.excelforum.com/member.php...fo&userid=8710 View this thread: http://www.excelforum.com/showthread...hreadid=271697 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to delte macros in another workbook | Excel Discussion (Misc queries) | |||
delte multiple blank rows in excel | Excel Worksheet Functions | |||
How to Delte Multiple Rows Given a Value Existing | Excel Programming | |||
How to Delte Multiple Rows Given a Value Existing | Excel Programming | |||
Delte blank rows between different sections of data | Excel Programming |