If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
deleting rows macro
I have around 3000 rows of data
If the background color of the cell in Col B is light blue (in the color palette, col 7 , row 4) , then it should delete the row thank you --- Message posted from http://www.ExcelForum.com/ |
#2
|
|||
|
|||
deleting rows macro
Hi
try the following macro: Sub delete_rows() Dim lastrow As Long Dim row_index As Long Application.ScreenUpdating = False lastrow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row For row_index = lastrow - 1 To 1 Step -1 If Cells(row_index, "B").interior.colorindex= 42 then Rows(row_index).delete End If Next Application.ScreenUpdating = True End Sub You may have to adapt the line If Cells(row_index, "B").interior.colorindex= 42 then to your specific colorindex (as my color palette looks differently and I'm not sure which color you have chosen). To get your colorindex try the following: - format a cell with this color and select this cell - open the VBA editor and in the immediate window enter ?Activecell.interior.colorindex -- Regards Frank Kabel Frankfurt, Germany I have around 3000 rows of data If the background color of the cell in Col B is light blue (in the color palette, col 7 , row 4) , then it should delete the row thank you --- Message posted from http://www.ExcelForum.com/ |
#3
|
|||
|
|||
deleting rows macro
fullymooned
try this code You may have to change the ColorIndex number as my color palate layout appears to be different to yours. Sub DelRows() Dim lRow As Long Application.ScreenUpdating = False For lRow = Cells.Find(what:="*", _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious).Row _ To 1 Step -1 If Cells(lRow, "b").Interior.ColorIndex = 34 Then Rows(lRow).Delete End If Next lRow Application.ScreenUpdating = True End Sub --- Message posted from http://www.ExcelForum.com/ |
Thread Tools | |
Display Modes | |
|
|