Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macros
I am new a writing macros and am having a problem writing one that will let me delete all rows that have a 0 value in cells of column "B" while leaving all of the other rows with cells that have values and are blank. Can anyone help here? -- TS |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macros
Put this in the standard code module1. Sub delZeros() Dim lr As Long, sh As Worksheet lr = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row Set sh = ActiveSheet For i = lr To 2 Step -1 With sh If .Range("B" & i).Value = 0 And .Range("B" & i) _ .Value < "" And Not IsEmpty(.Range("B" & i)) Then sh.Rows(i).Delete End If End With Next End Sub "TSVillanova" wrote in message ... I am new a writing macros and am having a problem writing one that will let me delete all rows that have a 0 value in cells of column "B" while leaving all of the other rows with cells that have values and are blank. Can anyone help here? -- TS |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macros
Now, I've been writing in VBA for Excel only a few years myself so I ask in ignorance. But if we already know CellObj.Value = 0, what point can there be in also asking whether CellObj.Value < "" or Not IsEmpty(CellObj)? Doesn't the first test tell us everything we need to know? Seems to me the two lines I've commented out below are simply wasted space. What am I missing? --- "JLGWhiz" wrote: Sub delZeros() lr = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row For i = lr To 2 Step -1 With ActiveSheet If .Range("B" & i).Value = 0 _ ' And .Range("B" & i).Value < "" _ ' And Not IsEmpty(.Range("B" & i)) _ Then .Rows(i).Delete End With Next i End Sub --- "TSVillanova" I am new a writing macros and am having a problem writing one that will let me delete all rows that have a 0 value in cells of column "B" while leaving all of the other rows with cells that have values and are blank. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macros
Works on the active sheet.. Sub DeleteRows() Dim lngRow as Long For lngRow = Cells(Rows.Count, "B").End(xlUp).Row To 1 Step -1 If Range("B" & lngRow).Text = "0" Then Rows(lngRow).Delete Next End Sub If this post helps click Yes --------------- Jacob Skaria "TSVillanova" wrote: I am new a writing macros and am having a problem writing one that will let me delete all rows that have a 0 value in cells of column "B" while leaving all of the other rows with cells that have values and are blank. Can anyone help here? -- TS |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macros
On the active sheet, identify a cell with absolutely nothing in it (let's say that cell is E1) then go to the Immediate window in the VB editor and execute this line... ? Range("E1").Value = 0 Notice the response is True even though the cell does not have a 0 value in it (as the OP stated is the condition that needs to be met). -- Rick (MVP - Excel) "Bob Bridges" wrote in message ... Now, I've been writing in VBA for Excel only a few years myself so I ask in ignorance. But if we already know CellObj.Value = 0, what point can there be in also asking whether CellObj.Value < "" or Not IsEmpty(CellObj)? Doesn't the first test tell us everything we need to know? Seems to me the two lines I've commented out below are simply wasted space. What am I missing? --- "JLGWhiz" wrote: Sub delZeros() lr = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row For i = lr To 2 Step -1 With ActiveSheet If .Range("B" & i).Value = 0 _ ' And .Range("B" & i).Value < "" _ ' And Not IsEmpty(.Range("B" & i)) _ Then .Rows(i).Delete End With Next i End Sub --- "TSVillanova" I am new a writing macros and am having a problem writing one that will let me delete all rows that have a 0 value in cells of column "B" while leaving all of the other rows with cells that have values and are blank. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macros
Work Great - Thanks "JLGWhiz" wrote: Put this in the standard code module1. Sub delZeros() Dim lr As Long, sh As Worksheet lr = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row Set sh = ActiveSheet For i = lr To 2 Step -1 With sh If .Range("B" & i).Value = 0 And .Range("B" & i) _ .Value < "" And Not IsEmpty(.Range("B" & i)) Then sh.Rows(i).Delete End If End With Next End Sub "TSVillanova" wrote in message ... I am new a writing macros and am having a problem writing one that will let me delete all rows that have a 0 value in cells of column "B" while leaving all of the other rows with cells that have values and are blank. Can anyone help here? -- TS |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macros
this is a VB default - its "coercion" -- if there's nothing in a cell , then
by coercion its value is zero .....kind of logical really. I'd rather use IF Range("E1").Value = "" Then or IF IsNull(Range("E1").Value) Then "Rick Rothstein" wrote in message ... On the active sheet, identify a cell with absolutely nothing in it (let's say that cell is E1) then go to the Immediate window in the VB editor and execute this line... ? Range("E1").Value = 0 Notice the response is True even though the cell does not have a 0 value in it (as the OP stated is the condition that needs to be met). -- Rick (MVP - Excel) "Bob Bridges" wrote in message ... Now, I've been writing in VBA for Excel only a few years myself so I ask in ignorance. But if we already know CellObj.Value = 0, what point can there be in also asking whether CellObj.Value < "" or Not IsEmpty(CellObj)? Doesn't the first test tell us everything we need to know? Seems to me the two lines I've commented out below are simply wasted space. What am I missing? --- "JLGWhiz" wrote: Sub delZeros() lr = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row For i = lr To 2 Step -1 With ActiveSheet If .Range("B" & i).Value = 0 _ ' And .Range("B" & i).Value < "" _ ' And Not IsEmpty(.Range("B" & i)) _ Then .Rows(i).Delete End With Next i End Sub --- "TSVillanova" I am new a writing macros and am having a problem writing one that will let me delete all rows that have a 0 value in cells of column "B" while leaving all of the other rows with cells that have values and are blank. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macros
The OP expressly stated that they wanted to retain rows with blank cells in
the search range. Since VBA can "see" the difference between the values of Empty, Empty String ("") and zero, the code had to be written so that VBA would ignore Empty and Empty String. "Patrick Molloy" wrote in message ... this is a VB default - its "coercion" -- if there's nothing in a cell , then by coercion its value is zero ....kind of logical really. I'd rather use IF Range("E1").Value = "" Then or IF IsNull(Range("E1").Value) Then "Rick Rothstein" wrote in message ... On the active sheet, identify a cell with absolutely nothing in it (let's say that cell is E1) then go to the Immediate window in the VB editor and execute this line... ? Range("E1").Value = 0 Notice the response is True even though the cell does not have a 0 value in it (as the OP stated is the condition that needs to be met). -- Rick (MVP - Excel) "Bob Bridges" wrote in message ... Now, I've been writing in VBA for Excel only a few years myself so I ask in ignorance. But if we already know CellObj.Value = 0, what point can there be in also asking whether CellObj.Value < "" or Not IsEmpty(CellObj)? Doesn't the first test tell us everything we need to know? Seems to me the two lines I've commented out below are simply wasted space. What am I missing? --- "JLGWhiz" wrote: Sub delZeros() lr = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row For i = lr To 2 Step -1 With ActiveSheet If .Range("B" & i).Value = 0 _ ' And .Range("B" & i).Value < "" _ ' And Not IsEmpty(.Range("B" & i)) _ Then .Rows(i).Delete End With Next i End Sub --- "TSVillanova" I am new a writing macros and am having a problem writing one that will let me delete all rows that have a 0 value in cells of column "B" while leaving all of the other rows with cells that have values and are blank. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macros in Personal.xls that would create two toolbars and buttonswith assigned macros | Excel Programming | |||
choose default macros Not Enabled / Macros Enable Setting | Excel Programming | |||
weird saving of a document with macros resulting with macros being transfered to the copy | Excel Programming | |||
convert lotus 123w macros to excel macros | Excel Programming | |||
convert lotus 123w macros to excel macros | Excel Programming |