How do you delete a row in a macro?

[Robert]

Hi everyone,

I'm trying to figure out how to delete an rows that
don't contain data that I want to keep. I wrote the
following macro but something is preventing me from
deleting the row... no errors occur and I don't know why
the selected rows are not being deleted. Does someone
know why this is happening?


Private Sub CommandButton1_Click()
Dim s(3) As String
Dim sLen(3) As Long
Dim i As Integer
Dim count As Integer
Dim ToBeDele() As String

s(0) = "...a"
s(1) = "...b
s(2) = " JOURNAL"
s(3) = "...c"

For i = 0 To 3 Step 1
sLen(i) = Len(s(i))
Next i

Sheets("GL20990 (2)").Activate
count = 1
For Each c In Worksheets("GL20990 (2)").Range("A1:A17")

b = False
For i = 0 To 3 Step 1
If Left(CStr(c.Value), sLen(i)) = s(i) Then
b = True
Exit For
End If
Next i

If Not b Then
If count = 1 Then
l = 0
Else: l = UBound(ToBeDele) + 1
End If
ReDim Preserve ToBeDele(l)
ToBeDele(l) = "A" & CStr(count)
End If
count = count + 1
Next c

'HERE IS MY PROBLEM! Why can't I delete the row?
For i = UBound(ToBeDele) To 0 Step -1
Set c = Range(CStr(ToBeDele(i)))
c.EntireRow.Delete
Next i

End Sub

 

[Greg Wilson]

I suggest the below simplification. However, note that
there is an apparent quirk with Excel: There seems to be a
difference between text with leading periods in a VBE code
module versus a worksheet cell. When I copied the text
with leading periods (e.g. "...a") from the code module
and pasted it to the worksheet my code worked. However,
when I typed it directly into the worksheet cells it
didn't - i.e. It's not recognized as the same. You'll have
to resolve this yourself.

As far as your code is concerned, when the above was taken
into account, it worked for me except that the loop at the
end that deletes the rows individually causes a
referencing error. When you delete rows individually then
the cell addresses change after each deletion. Then when
you go to delete the next row, the address you have stored
in the ToBeDele array is no longer valid. That's why I use
the non-contiguous DelRng variable and delete them all at
once to avoid this complication. Your code did delete rows
for me, just not the correct ones.

'Suggested simplified code:-
Private Sub CommandButton1_Click()
Dim i As Integer
Dim DelRng As Range
Dim C As Range, b As Boolean
Dim Arr As Variant

Arr = Array("...a", "...b", "...c", "JOURNAL")
For Each C In Worksheets("GL20990 (2)").Range("A1:A17")
b = False
For i = 0 To 3
If Trim(C) = Arr(i) Then b = True
Next
If Not b Then
If DelRng Is Nothing Then _
Set DelRng = C Else Set DelRng = Union(DelRng, C)
End If
Next C
DelRng.EntireRow.Delete
End Sub

Regards,
Greg

 

[Greg Wilson]

Correction to my post:

Robert,
I was too hasty in my statement that your code doesn't
delete the correct rows. I overlooked the fact that it
loops in reverse order which is another means of avoiding
the referencing errors. I just ran it again and it worked.
I'm not sure what I did wrong to get it to fail during
testing. In short, the only problem seems to be the
leading period issue.

Regards,
Greg