Re: Problem deleting rows by index

Hi Chris, sorry I realized that you were testing different approaches after I
posted (a duh moment for me). As a test, I executed a single line of code:
rows(2).delete on a spread*** and it worked fine (deleted the second row on
the spread***) so I don't think your syntax is wrong. Perhaps the problem
is with the index value "element"?

"Chris" wrote:

No, I don't think so. In the RemoveJunk() sub there are three differant ways
that I tried. I keep 2 of them commented while testing the third.

I'm pretty sure that none of them are correct


Worksheets("Sheet1").Cells.Rows(element).Delete <--this doesn't seem to do
much of anything

Rows(element).Delete <-- this doesn't seem to do much of anything

Worksheets("Sheet1").Cells(element).EntireRow.Delete <--This deletes all
kinds of rows that it shouldn't

What am I doing wrong here? I'm sure I have the syntax all wrong.

Thanks,

Chris



"Paul Mathews" <PaulMathews@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:B7715E6E-A9D5-4C7C-879A-1CF11FA984AC@xxxxxxxxxxxxxxxx
Chris, I noticed that you've got a single quote in front of the row delete
which makes it a comment. Could that be the issue?

"Chris" wrote:

Hello Everyone,

I'm new to excel vba programming, but have a little vb.net experience.

I'm trying to delete rows by index i.e. Rows(index).Delete, but nothing
is
ever deleted. The code doesn't give me an error and comes to a quiet
completion.


Thanks in advance for any help.

Chris


I might be posting more code than necessary, but because I'm a Excel
neophyte I'm thinking there might be a better way to accomplish this
task.

Here is the code in question:

Dim col As New Collection

Sub mainSub()
'parse entries in index.dat (index.dat holds IE's History)
'and copy to Excel Worksheet1
'parseIndex_dat(Computername, Username)

'Remove websites unrelated to the Internet
'and store row location in collection
FindJunk ("file://")
FindJunk ("res://")
FindJunk ("host:")
FindJunk ("outlook:")
FindJunk ("about:")

RemoveJunk 'This isn't working

FindAndHighlight "hotmail", 6
FindAndHighlight "aim", 7
FindAndHighlight "messenger", 10
FindAndHighlight "myspace", 46

End Sub

Sub FindJunk(strFind As String)
Dim c As Range
With Worksheets(1).Cells ' Whole sheet
Set c = .Find(strFind, LookIn:=xlValues, Lookat:=xlPart)
If Not c Is Nothing Then
firstAddress = c.Address
Do
col.Add (c.Row)
Set c = .FindNext(c)
Loop While c.Address <> firstAddress
End If
End With
End Sub

Sub RemoveJunk()
Dim element As Variant
For Each element In col
'Worksheets("Sheet1").Cells.Rows(element).Delete
'Rows(element).Delete
Worksheets("Sheet1").Cells(element).EntireRow.Delete

'How do I delete the rows? All of the above three
'don't give me a compile error

Next
End Sub

Sub FindAndHighlight(strFind As String, color As Integer)
Dim c As Range
With Worksheets(1).Cells
Set c = .Find(strFind, LookIn:=xlValues, Lookat:=xlPart)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.EntireRow.Interior.ColorIndex = color
Set c = .FindNext(c)
Loop While c.Address <> firstAddress
End If
End With
End Sub






.