Problem deleting rows by index
 

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.Dele te

'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

Problem deleting rows by index
 

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.Dele te

'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

Problem deleting rows by index
 

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.Dele te <--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" wrote in message
...
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.Dele te

'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

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 spreadsheet and it worked fine (deleted the second row on
the spreadsheet) 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.Dele te <--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" wrote in message
...
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.Dele te

'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

Problem deleting rows by index
 

assuming element is the number of the row you want to delete:

Worksheets("Sheet1").Rows(element).Delete


however, let's assume that col contains 1, 5, 15

when you delete Row 1, now the old row 5 is row 4 and the old row 15 is row
14

when you delete the new row 5, now the original row 15 is row 13.

See the problem.

here is a possibility:

Sub RemoveJunk()
Dim element As Variant
Dim rng as Range, rng1 as Range
For Each element In col
set rng1 = Worksheets("Sheet1").Cells(element,1)
if rng is nothing then
set rng = rng1
else
set rng = union(rng,rng1)
end if
Next
if not rng is nothing then
rng.EntireRow.Delete
End if
End Sub

--
Regards,
Tom Ogilvy


"Chris" wrote in message
...
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.Dele te <--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" wrote in message
...
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.Dele te

'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

Problem deleting rows by index
 

Tom, you hit the nail right on the head.

Thanks!

"Tom Ogilvy" wrote in message
...
assuming element is the number of the row you want to delete:

Worksheets("Sheet1").Rows(element).Delete


however, let's assume that col contains 1, 5, 15

when you delete Row 1, now the old row 5 is row 4 and the old row 15 is
row
14

when you delete the new row 5, now the original row 15 is row 13.

See the problem.

here is a possibility:

Sub RemoveJunk()
Dim element As Variant
Dim rng as Range, rng1 as Range
For Each element In col
set rng1 = Worksheets("Sheet1").Cells(element,1)
if rng is nothing then
set rng = rng1
else
set rng = union(rng,rng1)
end if
Next
if not rng is nothing then
rng.EntireRow.Delete
End if
End Sub

--
Regards,
Tom Ogilvy


"Chris" wrote in message
...
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.Dele te <--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" wrote in message
...
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.Dele te

'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

Problem deleting rows by index
 

Thanks for the help Paul :o)


"Paul Mathews" wrote in message
...
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 spreadsheet and it worked fine (deleted the second row
on
the spreadsheet) 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.Dele te <--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" wrote in message
...
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.Dele te

'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