Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows dependent upon cell contents
Forgot to mention that the original code was sent by Alok Joshi
-- Robert |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Directing cell contents to one of two cells dependent on its value | Excel Worksheet Functions | |||
directing cell contents dependent on its value | Excel Worksheet Functions | |||
Making cell contents dependent on another cell | Excel Discussion (Misc queries) | |||
Data Validation list dependent on contents of another cell | Excel Discussion (Misc queries) | |||
Change Cell Color dependent on Cell Contents | Excel Programming |