Deleting rows dependent upon cell contents

Hi,

I have a spreadsheet of 5 columns and 65536 rows, the columns are: date,
time, flow rate m3/hr, power kWh, pressure bar. Some of the rows have
redundent information when the unit is turned off so in the kWh column I will
have values of 0. I want to delete these rows from the data. What is the
easiest way to do this? I cannot be bothered going down all the rows and
finding the instances where the kWh is 0 on an individual basis.
--
daedalus1

Sort your data based on the kWh column then select and delete the relevant
rows. Resort the data as original (presumably by date). If there is no
column originally sorted, add another column numbered 1 to 65536 and use
this to restore the original order.

--
Ian
--
"daedalus1" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> I have a spreadsheet of 5 columns and 65536 rows, the columns are: date,
> time, flow rate m3/hr, power kWh, pressure bar. Some of the rows have
> redundent information when the unit is turned off so in the kWh column I
> will
> have values of 0. I want to delete these rows from the data. What is the
> easiest way to do this? I cannot be bothered going down all the rows and
> finding the instances where the kWh is 0 on an individual basis.
> --
> daedalus1

The following code was provided by Alok Joshi to HIDE rows meeting a
condition, in this case "x". Amend to suit your requirement. His is checking
2 colums and upto 10000 rows. (the code is the best I have seen so far for
speed).

Sub HideRows()
Dim i%, rng As Range
Set rng = Sheet1.Cells(1, 2)
For i = 2 To 10000
If Sheet1.Cells(i, 2) = "x" And Sheet1.Cells(i, 3) = "x" Then
Set rng = Union(rng, Sheet1.Cells(i, 2))
End If
Next i
rng.Rows.EntireRow.Hidden = True
End Sub

--
Robert


"Ian" wrote:

> Sort your data based on the kWh column then select and delete the relevant
> rows. Resort the data as original (presumably by date). If there is no
> column originally sorted, add another column numbered 1 to 65536 and use
> this to restore the original order.
>
> --
> Ian
> --
> "daedalus1" <[email protected]> wrote in message
> news:[email protected]...
> > Hi,
> >
> > I have a spreadsheet of 5 columns and 65536 rows, the columns are: date,
> > time, flow rate m3/hr, power kWh, pressure bar. Some of the rows have
> > redundent information when the unit is turned off so in the kWh column I
> > will
> > have values of 0. I want to delete these rows from the data. What is the
> > easiest way to do this? I cannot be bothered going down all the rows and
> > finding the instances where the kWh is 0 on an individual basis.
> > --
> > daedalus1
>
>
>

Try this. Rename your sheet to PWR. I have deliberately set rows range 2 to
65535.

Dim i%, rng As Range
Set rng = Worksheets("PWR").Cells(1, 4)
For i = 2 To 65535
If Worksheets("PWR").Cells(i, 4) = "0" Then
Set rng = Union(rng, Worksheets("PWR").Cells(i, 4))
End If
Next i
rng.Rows.EntireRow.Delete

End Sub

--
Robert


"daedalus1" wrote:

> Hi,
>
> I have a spreadsheet of 5 columns and 65536 rows, the columns are: date,
> time, flow rate m3/hr, power kWh, pressure bar. Some of the rows have
> redundent information when the unit is turned off so in the kWh column I will
> have values of 0. I want to delete these rows from the data. What is the
> easiest way to do this? I cannot be bothered going down all the rows and
> finding the instances where the kWh is 0 on an individual basis.
> --
> daedalus1

Forgot to mention that the original code was sent by Alok Joshi
--
Robert