Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I conditionally delete row in Excel? (if B3=0 delete row B)
I am trying to delete rows based on conditional statements. For example,
delete row B if cell B3(budget balance) is 0. I am having trouble figuring this out. Can I incorporate a macro that deletes a row into an "if" statement? Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I conditionally delete row in Excel? (if B3=0 delete row B)
Do you need a macro or can you do it manually?
If manually is ok: I'd use another column of Cells: =if(b2=0,"deleteme","keepme") and drag down. Then apply data|filter|autofilter to that helper column. And show the Deleteme rows. And delete those visible rows. Remove the filter and delete that helper column. Badger wrote: I am trying to delete rows based on conditional statements. For example, delete row B if cell B3(budget balance) is 0. I am having trouble figuring this out. Can I incorporate a macro that deletes a row into an "if" statement? Thanks. -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I conditionally delete row in Excel? (if B3=0 delete row B
Thanks. I need it to be automated so I think a macro is my only choice
unless I can somehow incorporate a conditional delete row function into a cell formula. "Dave Peterson" wrote: Do you need a macro or can you do it manually? If manually is ok: I'd use another column of Cells: =if(b2=0,"deleteme","keepme") and drag down. Then apply data|filter|autofilter to that helper column. And show the Deleteme rows. And delete those visible rows. Remove the filter and delete that helper column. Badger wrote: I am trying to delete rows based on conditional statements. For example, delete row B if cell B3(budget balance) is 0. I am having trouble figuring this out. Can I incorporate a macro that deletes a row into an "if" statement? Thanks. -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I conditionally delete row in Excel? (if B3=0 delete row B
Maybe something like:
Option Explicit Sub testme() Dim rng As Range Dim wks As Worksheet Dim myCell As Range Dim DelRng As Range Set wks = Worksheets("Sheet1") With wks Set rng = .Range("B2", .Cells(.Rows.Count, "B").End(xlUp)) End With For Each myCell In rng.Cells If IsEmpty(myCell) = False Then If myCell.Value = 0 Then If DelRng Is Nothing Then Set DelRng = myCell Else Set DelRng = Union(myCell, DelRng) End If End If End If Next myCell If DelRng Is Nothing Then 'do nothing Else DelRng.Select 'or (after testing!) 'DelRng.EntireRow.Delete End If End Sub Badger wrote: Thanks. I need it to be automated so I think a macro is my only choice unless I can somehow incorporate a conditional delete row function into a cell formula. "Dave Peterson" wrote: Do you need a macro or can you do it manually? If manually is ok: I'd use another column of Cells: =if(b2=0,"deleteme","keepme") and drag down. Then apply data|filter|autofilter to that helper column. And show the Deleteme rows. And delete those visible rows. Remove the filter and delete that helper column. Badger wrote: I am trying to delete rows based on conditional statements. For example, delete row B if cell B3(budget balance) is 0. I am having trouble figuring this out. Can I incorporate a macro that deletes a row into an "if" statement? Thanks. -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I conditionally delete row in Excel? (if B3=0 delete row B
Thank you so much!
"Dave Peterson" wrote: Maybe something like: Option Explicit Sub testme() Dim rng As Range Dim wks As Worksheet Dim myCell As Range Dim DelRng As Range Set wks = Worksheets("Sheet1") With wks Set rng = .Range("B2", .Cells(.Rows.Count, "B").End(xlUp)) End With For Each myCell In rng.Cells If IsEmpty(myCell) = False Then If myCell.Value = 0 Then If DelRng Is Nothing Then Set DelRng = myCell Else Set DelRng = Union(myCell, DelRng) End If End If End If Next myCell If DelRng Is Nothing Then 'do nothing Else DelRng.Select 'or (after testing!) 'DelRng.EntireRow.Delete End If End Sub Badger wrote: Thanks. I need it to be automated so I think a macro is my only choice unless I can somehow incorporate a conditional delete row function into a cell formula. "Dave Peterson" wrote: Do you need a macro or can you do it manually? If manually is ok: I'd use another column of Cells: =if(b2=0,"deleteme","keepme") and drag down. Then apply data|filter|autofilter to that helper column. And show the Deleteme rows. And delete those visible rows. Remove the filter and delete that helper column. Badger wrote: I am trying to delete rows based on conditional statements. For example, delete row B if cell B3(budget balance) is 0. I am having trouble figuring this out. Can I incorporate a macro that deletes a row into an "if" statement? Thanks. -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I conditionally delete rows based on cell contents? | Excel Worksheet Functions | |||
Conditionally delete | Excel Discussion (Misc queries) | |||
Conditionally Delete Cells From Named Range | Excel Programming | |||
Delete every 3rd row, then delete rows 2-7, move info f/every 2nd row up one to the end and delete the row below | Excel Programming | |||
conditionally delete some elements from an array | Excel Programming |