Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
MS Excel Delete row if criteria is meet
I have a table in the worksheet 'Central'.
This table currently occupies cells B7 to G1000. The length of the table may vary. So G10001 or more rows. The data found on first column of the table on column B7 to B????, there are a series of 'YES' or 'NO' or other text on each cell going down. Is it possible for a macro to go through each cell in column B7 to B???? in the table and delete the entire record row if the criteria text 'NO' is identified in the cell and remove the formatting and colors for the other rows which does not have 'NO' as a text. Can this be done by VBA and without the excel filters? Appreicate you help in advance... Many thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
MS Excel Delete row if criteria is meet
Hi,
This goes in a general module and works on the active sheet. You will need to add to the formatting conditions to remove others I never included. Sub delete_Me2() Dim copyrange As Range lastrow = Cells(Cells.Rows.Count, "B").End(xlUp).Row Set MyRange = Range("B7:B" & lastrow) For Each c In MyRange If UCase(c.Value) = "NO" Then If copyrange Is Nothing Then Set copyrange = c.EntireRow Else Set copyrange = Union(copyrange, c.EntireRow) End If End If If UCase(c.Value) = "YES" Then With c.EntireRow.Interior .Pattern = xlNone End With With c.EntireRow.Font .Name = "Arial" .Size = 11 End With End If Next If Not copyrange Is Nothing Then copyrange.Delete End If End Sub Mike "How Two" wrote: I have a table in the worksheet 'Central'. This table currently occupies cells B7 to G1000. The length of the table may vary. So G10001 or more rows. The data found on first column of the table on column B7 to B????, there are a series of 'YES' or 'NO' or other text on each cell going down. Is it possible for a macro to go through each cell in column B7 to B???? in the table and delete the entire record row if the criteria text 'NO' is identified in the cell and remove the formatting and colors for the other rows which does not have 'NO' as a text. Can this be done by VBA and without the excel filters? Appreicate you help in advance... Many thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete data in cells that don't meet criteria | New Users to Excel | |||
Delete rows that do not meet specific criteria | Excel Programming | |||
Delete Rows where cells does not meet criteria | Excel Worksheet Functions | |||
how do i delete rows when cells meet certain criteria? | Excel Programming | |||
Excel 2000 VBA - Delete Rows That Meet Criteria | Excel Programming |