Macros

  • Thread starter Thread starter TSVillanova
  • Start date Start date
T

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.

Can anyone help here?
 
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
 
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?
 
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
 
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).
 
Work Great - Thanks

JLGWhiz said:
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
Click to expand...
 
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
 
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.
 
Back
Top