Prev: Identify Differences Between four columns of data
Next: Losing Conditional Formatting
From: Grey Old Man on 22 Apr 2010 10:08
I am importing a variable amount (10,000+) rows of source data into an Excel
2002 worksheet. Many of the rows will contain zero in column B (total
quantity) or zero in column C (total cost) which are not required. How can I
automatically delete rows that contain zeros in BOTH columns?
The intention is then to rank the remaining data.

Thanks in anticipation.
From: Jim Thomlinson on 22 Apr 2010 10:18
There is no automatic way to delete rows. In the end that will be a process
of steps of some sort. If you are getting 10,000 + rows of source data then I
assume that it is coming from a data base of some sort. How about modifying
the query to exclude those records. If that is not possible then probably the
easiest would just to be to sort the data pulling the zeros to the top and
then delete those rows. Other possibilites would be to use an auto filter or
a pivot table...
--
HTH...

Jim Thomlinson


"Grey Old Man" wrote:

> I am importing a variable amount (10,000+) rows of source data into an Excel
> 2002 worksheet. Many of the rows will contain zero in column B (total
> quantity) or zero in column C (total cost) which are not required. How can I
> automatically delete rows that contain zeros in BOTH columns?
> The intention is then to rank the remaining data.
>
> Thanks in anticipation.
From: Mike H on 22 Apr 2010 10:29
Hi,

How about a macro. Alt+F11 to open VB editor. Right click 'ThisWorkbook' and
insert module and paste the code in. Change the sheet to the correct one and
run the code. I never actually bothered to distinguish between blank cells
and zero because in this application I don't think it mmatters.

Sub delete_Me()
Dim CopyRange As Range
Dim LastRow As Long
Set sht = Sheets("Sheet1") ' change to suit
LastRow = sht.Cells(Cells.Rows.Count, "B").End(xlUp).Row
Set MyRange = sht.Range("B1:B" & LastRow)
For Each c In MyRange
If c.Value = 0 And c.Offset(, 1).Value = 0 Then
If CopyRange Is Nothing Then
Set CopyRange = c.EntireRow
Else
Set CopyRange = Union(CopyRange, c.EntireRow)
End If
End If
Next
If Not CopyRange Is Nothing Then
CopyRange.Delete
End If
End Sub

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Grey Old Man" wrote:

> I am importing a variable amount (10,000+) rows of source data into an Excel
> 2002 worksheet. Many of the rows will contain zero in column B (total
> quantity) or zero in column C (total cost) which are not required. How can I
> automatically delete rows that contain zeros in BOTH columns?
> The intention is then to rank the remaining data.
>
> Thanks in anticipation.
 | 
Pages: 1
Prev: Identify Differences Between four columns of data
Next: Losing Conditional Formatting