Deleting rows dependent upon cell contents
 

Hi,

I have a spreadsheet of 5 columns and 65536 rows, the columns a 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

Deleting rows dependent upon cell contents
 

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" wrote in message
...
Hi,

I have a spreadsheet of 5 columns and 65536 rows, the columns a 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

Deleting rows dependent upon cell contents
 

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" wrote in message
...
Hi,

I have a spreadsheet of 5 columns and 65536 rows, the columns a 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

Deleting rows dependent upon cell contents
 

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 a 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

Deleting rows dependent upon cell contents
 

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