Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Deleting rows which are not highlighted
Dear Experts
I have a huge data to work excel running into 8 thousand rows, I run a unique macro which highlights the data which are of interest. This macro changes the color of the first coloumn. Now I need a further request to document such differences which means I need to delete all the rows whose first row is not yellow. Can I get some help please. Existing Conditions 1. Data running in 8 thousand+ or more rows. 2. Some rows has only FIRST COLUMN highlighted yellow because of the macro I run Requirements: Now I need to Delete rest of all the rows whose FIRST COLUMN is NOT Yellow. Please can I get help. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows which are not highlighted
Hi,
Am Mon, 4 Feb 2013 07:38:43 +0000 schrieb protocoder: 2. Some rows has only FIRST COLUMN highlighted yellow because of the macro I run instead of macro to highlight run macro to delete rows If not condition to highlight then entirerow.delete Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows which are not highlighted
On Sunday, February 3, 2013 11:38:43 PM UTC-8, protocoder wrote:
Dear Experts I have a huge data to work excel running into 8 thousand rows, I run a unique macro which highlights the data which are of interest. This macro changes the color of the first coloumn. Now I need a further request to document such differences which means I need to delete all the rows whose first row is not yellow. Can I get some help please. Existing Conditions 1. Data running in 8 thousand+ or more rows. 2. Some rows has only FIRST COLUMN highlighted yellow because of the macro I run Requirements: Now I need to Delete rest of all the rows whose FIRST COLUMN is NOT Yellow. Please can I get help. -- protocoder Seems like Claus' solution make good sense. If that doesn't work for you try this. Option Explicit Sub NoYellar() Dim lRow As Long Dim c As Range With Sheets("sheet1") lRow = .Cells(.Rows.Count, 1).End(xlUp).Row For Each c In .Range("A1:A" & lRow) If c.Interior.ColorIndex < 6 Then c.EntireRow.ClearContents End If Next End With End Sub Regards, Howard |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows which are not highlighted
Hi Howard,
Am Mon, 4 Feb 2013 00:43:27 -0800 (PST) schrieb Howard: Sub NoYellar() Dim lRow As Long Dim c As Range With Sheets("sheet1") lRow = .Cells(.Rows.Count, 1).End(xlUp).Row For Each c In .Range("A1:A" & lRow) If c.Interior.ColorIndex < 6 Then c.EntireRow.ClearContents End If Next End With End Sub to avoid looping through all cells you can filter and delete (xl2007 or later): Sub DeleteRows() Dim LRow As Long Dim LCol As Integer With ActiveSheet LRow = .Cells(.Rows.Count, 1).End(xlUp).Row LCol = .Cells(1, .Columns.Count).End(xlToLeft).Column .Range(.Cells(1, 1), .Cells(LRow, LCol)).AutoFilter Field:=1, _ Operator:=xlFilterNoFill Range(.Cells(2, 1), .Cells(LRow, 1)) _ .SpecialCells(xlCellTypeVisible).EntireRow.Delete .AutoFilterMode = False End With End Sub Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows which are not highlighted
On Monday, February 4, 2013 1:01:42 AM UTC-8, Claus Busch wrote:
Hi Howard, Am Mon, 4 Feb 2013 00:43:27 -0800 (PST) schrieb Howard: Sub NoYellar() Dim lRow As Long Dim c As Range With Sheets("sheet1") lRow = .Cells(.Rows.Count, 1).End(xlUp).Row For Each c In .Range("A1:A" & lRow) If c.Interior.ColorIndex < 6 Then c.EntireRow.ClearContents End If Next End With End Sub to avoid looping through all cells you can filter and delete (xl2007 or later): Sub DeleteRows() Dim LRow As Long Dim LCol As Integer With ActiveSheet LRow = .Cells(.Rows.Count, 1).End(xlUp).Row LCol = .Cells(1, .Columns.Count).End(xlToLeft).Column .Range(.Cells(1, 1), .Cells(LRow, LCol)).AutoFilter Field:=1, _ Operator:=xlFilterNoFill Range(.Cells(2, 1), .Cells(LRow, 1)) _ .SpecialCells(xlCellTypeVisible).EntireRow.Delete .AutoFilterMode = False End With End Sub Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 That's pretty slick, dealing with 8K + rows I am sure makes makes a huge difference. I'll arcive that and give it a study, understand most of it but as a whole it's a bit above my pay grade. I was taken aback because it does not refer to interior.colorindex but the xlFilterNoFill I'm guessing takes care of that. Thanks for the info. Regards, Howard |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Too many rows highlighted | Excel Worksheet Functions | |||
More than selected rows being highlighted | Excel Discussion (Misc queries) | |||
highlighted rows excel | Excel Discussion (Misc queries) | |||
Highlighted rows | Excel Discussion (Misc queries) | |||
Deleting Non-Highlighted Cells | Excel Discussion (Misc queries) |