why doesn't this simple macro work in excel 2007?
 

I wrote this simple macro to just go through column B and delete rows that
don't have an email in them. It won't even compile. It tells me error
"Next without for".



Sub FindEmail()
'try to separate email addresses and add a comma at the end of them
FinalRow = Cells(65536, 2).End(xlUp).Row

For i = 1 To FinalRow
Cells(i, 2).Select
If Isblank.Cells(i, 2) Then
Range(i, 2).Select.Delete
Else
If Cells(i, 2).Value < "@" Then
Range(i, 2).Select.Delete
End If
Next i
End Sub

why doesn't this simple macro work in excel 2007?
 

hi
you left out a end if. vb will do that to you ie give the wrong error in
this situation. i don't know why but it will do the same in a do loop ie
leaving out an end if and tell you 'loop without a do'.

regards
FSt1

"ManhattanRebel" wrote:

I wrote this simple macro to just go through column B and delete rows that
don't have an email in them. It won't even compile. It tells me error
"Next without for".



Sub FindEmail()
'try to separate email addresses and add a comma at the end of them
FinalRow = Cells(65536, 2).End(xlUp).Row

For i = 1 To FinalRow
Cells(i, 2).Select
If Isblank.Cells(i, 2) Then
Range(i, 2).Select.Delete
Else
If Cells(i, 2).Value < "@" Then
Range(i, 2).Select.Delete
End If
Next i
End Sub

why doesn't this simple macro work in excel 2007?
 

Thank you, FSt1. That helped. I'm still having a problem. The second
command of Range(i, w). Select.Delete did not work. I substituted this
command: ActiveCell.EntireRow.Delete. That doesn't work, either. Are the
commands wrong, or is the previous line of code not instructing properly? I
am trying to determine if a cell has an email address in it. That is why I
ask if the value<"@", but I may need more in it. Any help appreciated.

-----------------------------------------------------------------------------
"FSt1" wrote:

hi
you left out a end if. vb will do that to you ie give the wrong error in
this situation. i don't know why but it will do the same in a do loop ie
leaving out an end if and tell you 'loop without a do'.

regards
FSt1

"ManhattanRebel" wrote:

I wrote this simple macro to just go through column B and delete rows that
don't have an email in them. It won't even compile. It tells me error
"Next without for".



Sub FindEmail()
'try to separate email addresses and add a comma at the end of them
FinalRow = Cells(65536, 2).End(xlUp).Row

For i = 1 To FinalRow
Cells(i, 2).Select
If Isblank.Cells(i, 2) Then
Range(i, 2).Select.Delete
Else
If Cells(i, 2).Value < "@" Then
Range(i, 2).Select.Delete
End If
Next i
End Sub

why doesn't this simple macro work in excel 2007?
 

hi
as to the second command. i noticed that and forgot to say somthing. sorry.
you don't need to select and delete . just delete.
Range(i, w).Delete
selecting sometimes slows things down. and vb knows were and what to do by
reference so selecting is most often not needed.
as to the @ find, try something like this....
If Left(Cells(i,2), 1) = "@" Then
MsgBox "it's ok"
End If

regards
FSt1

"ManhattanRebel" wrote:

Thank you, FSt1. That helped. I'm still having a problem. The second
command of Range(i, w). Select.Delete did not work. I substituted this
command: ActiveCell.EntireRow.Delete. That doesn't work, either. Are the
commands wrong, or is the previous line of code not instructing properly? I
am trying to determine if a cell has an email address in it. That is why I
ask if the value<"@", but I may need more in it. Any help appreciated.

-----------------------------------------------------------------------------
"FSt1" wrote:

hi
you left out a end if. vb will do that to you ie give the wrong error in
this situation. i don't know why but it will do the same in a do loop ie
leaving out an end if and tell you 'loop without a do'.

regards
FSt1

"ManhattanRebel" wrote:

I wrote this simple macro to just go through column B and delete rows that
don't have an email in them. It won't even compile. It tells me error
"Next without for".



Sub FindEmail()
'try to separate email addresses and add a comma at the end of them
FinalRow = Cells(65536, 2).End(xlUp).Row

For i = 1 To FinalRow
Cells(i, 2).Select
If Isblank.Cells(i, 2) Then
Range(i, 2).Select.Delete
Else
If Cells(i, 2).Value < "@" Then
Range(i, 2).Select.Delete
End If
Next i
End Sub

why doesn't this simple macro work in excel 2007?
 

hi
the msgbox was for test perposes only. should have mentioned that.

regards
FSt1

"FSt1" wrote:

hi
as to the second command. i noticed that and forgot to say somthing. sorry.
you don't need to select and delete . just delete.
Range(i, w).Delete
selecting sometimes slows things down. and vb knows were and what to do by
reference so selecting is most often not needed.
as to the @ find, try something like this....
If Left(Cells(i,2), 1) = "@" Then
MsgBox "it's ok"
End If

regards
FSt1

"ManhattanRebel" wrote:

Thank you, FSt1. That helped. I'm still having a problem. The second
command of Range(i, w). Select.Delete did not work. I substituted this
command: ActiveCell.EntireRow.Delete. That doesn't work, either. Are the
commands wrong, or is the previous line of code not instructing properly? I
am trying to determine if a cell has an email address in it. That is why I
ask if the value<"@", but I may need more in it. Any help appreciated.

-----------------------------------------------------------------------------
"FSt1" wrote:

hi
you left out a end if. vb will do that to you ie give the wrong error in
this situation. i don't know why but it will do the same in a do loop ie
leaving out an end if and tell you 'loop without a do'.

regards
FSt1

"ManhattanRebel" wrote:

I wrote this simple macro to just go through column B and delete rows that
don't have an email in them. It won't even compile. It tells me error
"Next without for".



Sub FindEmail()
'try to separate email addresses and add a comma at the end of them
FinalRow = Cells(65536, 2).End(xlUp).Row

For i = 1 To FinalRow
Cells(i, 2).Select
If Isblank.Cells(i, 2) Then
Range(i, 2).Select.Delete
Else
If Cells(i, 2).Value < "@" Then
Range(i, 2).Select.Delete
End If
Next i
End Sub

why doesn't this simple macro work in excel 2007?
 

hi
forget the left thing.
instead..
If Cells(i, 1) = WorksheetFunction.Find("@", Cells(i, 1), 2) < 1 Then
MsgBox "don't delete row"
End If
works better.

regards
FSt1


"FSt1" wrote:

hi
as to the second command. i noticed that and forgot to say somthing. sorry.
you don't need to select and delete . just delete.
Range(i, w).Delete
selecting sometimes slows things down. and vb knows were and what to do by
reference so selecting is most often not needed.
as to the @ find, try something like this....
If Left(Cells(i,2), 1) = "@" Then
MsgBox "it's ok"
End If

regards
FSt1

"ManhattanRebel" wrote:

Thank you, FSt1. That helped. I'm still having a problem. The second
command of Range(i, w). Select.Delete did not work. I substituted this
command: ActiveCell.EntireRow.Delete. That doesn't work, either. Are the
commands wrong, or is the previous line of code not instructing properly? I
am trying to determine if a cell has an email address in it. That is why I
ask if the value<"@", but I may need more in it. Any help appreciated.

-----------------------------------------------------------------------------
"FSt1" wrote:

hi
you left out a end if. vb will do that to you ie give the wrong error in
this situation. i don't know why but it will do the same in a do loop ie
leaving out an end if and tell you 'loop without a do'.

regards
FSt1

"ManhattanRebel" wrote:

I wrote this simple macro to just go through column B and delete rows that
don't have an email in them. It won't even compile. It tells me error
"Next without for".



Sub FindEmail()
'try to separate email addresses and add a comma at the end of them
FinalRow = Cells(65536, 2).End(xlUp).Row

For i = 1 To FinalRow
Cells(i, 2).Select
If Isblank.Cells(i, 2) Then
Range(i, 2).Select.Delete
Else
If Cells(i, 2).Value < "@" Then
Range(i, 2).Select.Delete
End If
Next i
End Sub

why doesn't this simple macro work in excel 2007?
 

I don't think you meant this syntax.

And you could use instr instead of worksheetfunction.find to stay with native
VBA.

FSt1 wrote:

hi
forget the left thing.
instead..
If Cells(i, 1) = WorksheetFunction.Find("@", Cells(i, 1), 2) < 1 Then
MsgBox "don't delete row"
End If
works better.

regards
FSt1

"FSt1" wrote:

hi
as to the second command. i noticed that and forgot to say somthing. sorry.
you don't need to select and delete . just delete.
Range(i, w).Delete
selecting sometimes slows things down. and vb knows were and what to do by
reference so selecting is most often not needed.
as to the @ find, try something like this....
If Left(Cells(i,2), 1) = "@" Then
MsgBox "it's ok"
End If

regards
FSt1

"ManhattanRebel" wrote:

Thank you, FSt1. That helped. I'm still having a problem. The second
command of Range(i, w). Select.Delete did not work. I substituted this
command: ActiveCell.EntireRow.Delete. That doesn't work, either. Are the
commands wrong, or is the previous line of code not instructing properly? I
am trying to determine if a cell has an email address in it. That is why I
ask if the value<"@", but I may need more in it. Any help appreciated.

-----------------------------------------------------------------------------
"FSt1" wrote:

hi
you left out a end if. vb will do that to you ie give the wrong error in
this situation. i don't know why but it will do the same in a do loop ie
leaving out an end if and tell you 'loop without a do'.

regards
FSt1

"ManhattanRebel" wrote:

I wrote this simple macro to just go through column B and delete rows that
don't have an email in them. It won't even compile. It tells me error
"Next without for".



Sub FindEmail()
'try to separate email addresses and add a comma at the end of them
FinalRow = Cells(65536, 2).End(xlUp).Row

For i = 1 To FinalRow
Cells(i, 2).Select
If Isblank.Cells(i, 2) Then
Range(i, 2).Select.Delete
Else
If Cells(i, 2).Value < "@" Then
Range(i, 2).Select.Delete
End If
Next i
End Sub


--

Dave Peterson

why doesn't this simple macro work in excel 2007?
 

If Isblank.Cells(i, 2) Then

Range(i, 2).Select.Delete


Hi. Since you don't want any blanks, I might get rid of those first.
Then, the rest of the code can skip this check.
This may not be correct, but may give you some ideas..

Sub Demo()
Dim Cell
Const K As String = "*@*"

'// Get rid of blanks first:
On Error Resume Next
[B:B].SpecialCells(xlCellTypeBlanks).EntireRow.Delete
ActiveSheet.UsedRange

'// Now, just test the remaining data
For Each Cell In [B:B].SpecialCells(xlCellTypeConstants).Cells
If Not (Cell Like K) Then Cell.Clear
Next Cell

[B:B].SpecialCells(xlCellTypeBlanks).EntireRow.Delete
ActiveSheet.UsedRange
End Sub


I don't know the answer to this one. Anyone?

If a cell has some text like abc@xyz
Then Excel sees this as a Hyperlink.
(The idea being we can assume this is ok for the op)

When looking at a cell's properties, I don't see any easy test for the cell being a Hyperlink.
Does anyone?

The closest I can find is based on this "Hyperlinks Object" help topic in Excel 2007.

For Each h in Worksheets(1).Hyperlinks

--
Dana DeLouis


"Dave Peterson" wrote in message ...

I don't think you meant this syntax.

And you could use instr instead of worksheetfunction.find to stay with native
VBA.

FSt1 wrote:


hi
forget the left thing.
instead..
If Cells(i, 1) = WorksheetFunction.Find("@", Cells(i, 1), 2) < 1 Then
MsgBox "don't delete row"
End If
works better.

regards
FSt1

"FSt1" wrote:


hi
as to the second command. i noticed that and forgot to say somthing. sorry.
you don't need to select and delete . just delete.
Range(i, w).Delete
selecting sometimes slows things down. and vb knows were and what to do by
reference so selecting is most often not needed.
as to the @ find, try something like this....
If Left(Cells(i,2), 1) = "@" Then
MsgBox "it's ok"
End If

regards
FSt1

"ManhattanRebel" wrote:

Thank you, FSt1. That helped. I'm still having a problem. The second
command of Range(i, w). Select.Delete did not work. I substituted this
command: ActiveCell.EntireRow.Delete. That doesn't work, either. Are the
commands wrong, or is the previous line of code not instructing properly? I
am trying to determine if a cell has an email address in it. That is why I
ask if the value<"@", but I may need more in it. Any help appreciated.

-----------------------------------------------------------------------------
"FSt1" wrote:

hi
you left out a end if. vb will do that to you ie give the wrong error in
this situation. i don't know why but it will do the same in a do loop ie
leaving out an end if and tell you 'loop without a do'.

regards
FSt1

"ManhattanRebel" wrote:

I wrote this simple macro to just go through column B and delete rows that
don't have an email in them. It won't even compile. It tells me error
"Next without for".



Sub FindEmail()
'try to separate email addresses and add a comma at the end of them
FinalRow = Cells(65536, 2).End(xlUp).Row

For i = 1 To FinalRow
Cells(i, 2).Select
If Isblank.Cells(i, 2) Then
Range(i, 2).Select.Delete
Else
If Cells(i, 2).Value < "@" Then
Range(i, 2).Select.Delete
End If
Next i
End Sub



--

Dave Peterson

why doesn't this simple macro work in excel 2007?
 

If you want to test to see if a cell contains an Insert|hyperlink type
hyperlink, you could use:

if somecell.Hyperlinks.Count 0 then



Dana DeLouis wrote:

If Isblank.Cells(i, 2) Then
Range(i, 2).Select.Delete

Hi. Since you don't want any blanks, I might get rid of those first.
Then, the rest of the code can skip this check.
This may not be correct, but may give you some ideas..

Sub Demo()
Dim Cell
Const K As String = "*@*"

'// Get rid of blanks first:
On Error Resume Next
[B:B].SpecialCells(xlCellTypeBlanks).EntireRow.Delete
ActiveSheet.UsedRange

'// Now, just test the remaining data
For Each Cell In [B:B].SpecialCells(xlCellTypeConstants).Cells
If Not (Cell Like K) Then Cell.Clear
Next Cell

[B:B].SpecialCells(xlCellTypeBlanks).EntireRow.Delete
ActiveSheet.UsedRange
End Sub

I don't know the answer to this one. Anyone?

If a cell has some text like abc@xyz
Then Excel sees this as a Hyperlink.
(The idea being we can assume this is ok for the op)

When looking at a cell's properties, I don't see any easy test for the cell being a Hyperlink.
Does anyone?

The closest I can find is based on this "Hyperlinks Object" help topic in Excel 2007.

For Each h in Worksheets(1).Hyperlinks

--
Dana DeLouis

"Dave Peterson" wrote in message ...
I don't think you meant this syntax.

And you could use instr instead of worksheetfunction.find to stay with native
VBA.

FSt1 wrote:

hi
forget the left thing.
instead..
If Cells(i, 1) = WorksheetFunction.Find("@", Cells(i, 1), 2) < 1 Then
MsgBox "don't delete row"
End If
works better.

regards
FSt1

"FSt1" wrote:

hi
as to the second command. i noticed that and forgot to say somthing. sorry.
you don't need to select and delete . just delete.
Range(i, w).Delete
selecting sometimes slows things down. and vb knows were and what to do by
reference so selecting is most often not needed.
as to the @ find, try something like this....
If Left(Cells(i,2), 1) = "@" Then
MsgBox "it's ok"
End If

regards
FSt1

"ManhattanRebel" wrote:

Thank you, FSt1. That helped. I'm still having a problem. The second
command of Range(i, w). Select.Delete did not work. I substituted this
command: ActiveCell.EntireRow.Delete. That doesn't work, either. Are the
commands wrong, or is the previous line of code not instructing properly? I
am trying to determine if a cell has an email address in it. That is why I
ask if the value<"@", but I may need more in it. Any help appreciated.

-----------------------------------------------------------------------------
"FSt1" wrote:

hi
you left out a end if. vb will do that to you ie give the wrong error in
this situation. i don't know why but it will do the same in a do loop ie
leaving out an end if and tell you 'loop without a do'.

regards
FSt1

"ManhattanRebel" wrote:

I wrote this simple macro to just go through column B and delete rows that
don't have an email in them. It won't even compile. It tells me error
"Next without for".



Sub FindEmail()
'try to separate email addresses and add a comma at the end of them
FinalRow = Cells(65536, 2).End(xlUp).Row

For i = 1 To FinalRow
Cells(i, 2).Select
If Isblank.Cells(i, 2) Then
Range(i, 2).Select.Delete
Else
If Cells(i, 2).Value < "@" Then
Range(i, 2).Select.Delete
End If
Next i
End Sub


--

Dave Peterson

--

Dave Peterson