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

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

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.

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

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.

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

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.

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.