Excel Macros - Partial strings/characters

sclark

I'm trying to write a macro that will delete an entire row based on the first letter in the first cell.

my loop/delete is fine i just can't figure out how to get ahold of the first letter in my ActiveCell.

i've tried activecell.characters.value and using instr(1, A1, "(letter)") but neither really worked.

say A1 contains "WERD", how can the macro tell if the first letter is "W" or not?

Thanks,

Sam

dokken2

: I'm trying to write a macro that will delete an entire row based on
: the first letter in the first cell.
:
: my loop/delete is fine i just can't figure out how to get ahold of
: the first letter in my ActiveCell.
:
: i've tried activecell.characters.value and using instr(1, A1,
: "(letter)") but neither really worked.
:
: say A1 contains "WERD", how can the macro tell if the first letter
: is "W" or not?
:
: Thanks,
:
: Sam
:

use the LEFT string func.

If Left(activecell.characters.value,1)="W" Then
'do something
EndIf

you may even need to convert the character to lower or upper case to catch both "W" and "w"-

If LCase(Left(activecell.characters.value,1))="w" Then
'do something
EndIf

sclark

My code is as follows, when i select a cell and run this macro, it fails.

Sub cleanup()

If Not Left(ActiveCell.Characters.Value, 1) = "W" Then
ActiveCell.EntireRow.Delete
End If

End Sub

Antyone know why this doesn't work?


: : I'm trying to write a macro that will delete an entire row based on
: : the first letter in the first cell.
: :
: : my loop/delete is fine i just can't figure out how to get ahold of
: : the first letter in my ActiveCell.
: :
: : i've tried activecell.characters.value and using instr(1, A1,
: : "(letter)") but neither really worked.
: :
: : say A1 contains "WERD", how can the macro tell if the first letter
: : is "W" or not?
: :
: : Thanks,
: :
: : Sam
: :
:
: use the LEFT string func.
:
: If Left(activecell.characters.value,1)="W" Then
: 'do something
: EndIf
:
: you may even need to convert the character to lower or upper case to
: catch both "W" and "w"-
:
: If LCase(Left(activecell.characters.value,1))="w" Then
: 'do something
: EndIf

dokken2

: My code is as follows, when i select a cell and run this macro, it
: fails.
:
: Sub cleanup()
:
: If Not Left(ActiveCell.Characters.Value, 1) = "W" Then
: ActiveCell.EntireRow.Delete
: End If
:
: End Sub
:
: Antyone know why this doesn't work?
:
:
couple things- there is no Value property but a Text property exists.
if using the Text property, code will fail if theres a number in the cell so need to handle a couple cases-


Dim tmp As Variant
tmp = ActiveCell

'empty cell?
If IsNull(tmp) Or IsEmpty(tmp) Then Exit Sub

'number?
If IsNumeric(tmp) Then Exit Sub

'delete only if "W"
If Left(tmp, 1) = "W" Then ActiveCell.EntireRow.Delete