I am trying to get this code to delete every row if the value in
column B begins with "X1".

I'm getting a syntax error any help would greatly be appreciated

Error here - Select Case Left((c, 2), 2)



Complete code:



Sub DeleteRowCase()
Dim c As Long
Dim LastRow As Long

Application.ScreenUpdating = False
LastRow = Range("C65536").End(xlUp).Row
For c = LastRow To 2 Step -1

Select Case Left((c, 2), 2)

Case Is = "X1": .EntireRow.Delete

Next c

End Select

Application.ScreenUpdating = True


End Sub

Select Case Left(cells(c, 2).value, 2)

or maybe better:

Select Case ucase(Left(cells(c, 2).value, 2))

And later...

Case Is = "X1": rows(c).Delete
or
Case Is = "X1": cells(c,2).EntireRow.Delete


Little Penny wrote:
>
> I am trying to get this code to delete every row if the value in
> column B begins with "X1".
>
> I'm getting a syntax error any help would greatly be appreciated
>
> Error here - Select Case Left((c, 2), 2)
>
> Complete code:
>
> Sub DeleteRowCase()
> Dim c As Long
> Dim LastRow As Long
>
> Application.ScreenUpdating = False
> LastRow = Range("C65536").End(xlUp).Row
> For c = LastRow To 2 Step -1
>
> Select Case Left((c, 2), 2)
>
> Case Is = "X1": .EntireRow.Delete
>
> Next c
>
> End Select
>
> Application.ScreenUpdating = True
>
>
> End Sub

--

Dave Peterson

Sub deleteif()
For i = Cells(Rows.count, "c").End(xlUp).Row To 2 Step -1
Select Case UCase(Left(Cells(i, "c"), 2))
Case Is = "X1": Rows(i).Delete
Case Else
End Select
Next i
End Sub



--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Little Penny" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I am trying to get this code to delete every row if the value in
> column B begins with "X1".
>
> I'm getting a syntax error any help would greatly be appreciated
>
> Error here - Select Case Left((c, 2), 2)
>
>
>
> Complete code:
>
>
>
> Sub DeleteRowCase()
> Dim c As Long
> Dim LastRow As Long
>
> Application.ScreenUpdating = False
> LastRow = Range("C65536").End(xlUp).Row
> For c = LastRow To 2 Step -1
>
> Select Case Left((c, 2), 2)
>
> Case Is = "X1": .EntireRow.Delete
>
> Next c
>
> End Select
>
> Application.ScreenUpdating = True
>
>
> End Sub

Thank you Dave.





On Sat, 19 Jan 2008 14:29:29 -0600, Dave Peterson
<(E-Mail Removed)> wrote:

>Select Case Left(cells(c, 2).value, 2)
>
>or maybe better:
>
>Select Case ucase(Left(cells(c, 2).value, 2))
>
>And later...
>
>Case Is = "X1": rows(c).Delete
>or
>Case Is = "X1": cells(c,2).EntireRow.Delete
>
>
>Little Penny wrote:
>>
>> I am trying to get this code to delete every row if the value in
>> column B begins with "X1".
>>
>> I'm getting a syntax error any help would greatly be appreciated
>>
>> Error here - Select Case Left((c, 2), 2)
>>
>> Complete code:
>>
>> Sub DeleteRowCase()
>> Dim c As Long
>> Dim LastRow As Long
>>
>> Application.ScreenUpdating = False
>> LastRow = Range("C65536").End(xlUp).Row
>> For c = LastRow To 2 Step -1
>>
>> Select Case Left((c, 2), 2)
>>
>> Case Is = "X1": .EntireRow.Delete
>>
>> Next c
>>
>> End Select
>>
>> Application.ScreenUpdating = True
>>
>>
>> End Sub

Question.

I'm not a vba guru but why does the code work with declaring i as a
variable, and what is c?

Thanks




On Sat, 19 Jan 2008 14:51:42 -0600, "Don Guillett"
<(E-Mail Removed)> wrote:

>Sub deleteif()
> For i = Cells(Rows.count, "c").End(xlUp).Row To 2 Step -1
> Select Case UCase(Left(Cells(i, "c"), 2))
> Case Is = "X1": Rows(i).Delete
> Case Else
> End Select
> Next i
>End Sub
>
>
>
> --
>Don Guillett
>Microsoft MVP Excel
>SalesAid Software
>(E-Mail Removed)
>"Little Penny" <(E-Mail Removed)> wrote in message
>news:(E-Mail Removed)...
>>I am trying to get this code to delete every row if the value in
>> column B begins with "X1".
>>
>> I'm getting a syntax error any help would greatly be appreciated
>>
>> Error here - Select Case Left((c, 2), 2)
>>
>>
>>
>> Complete code:
>>
>>
>>
>> Sub DeleteRowCase()
>> Dim c As Long
>> Dim LastRow As Long
>>
>> Application.ScreenUpdating = False
>> LastRow = Range("C65536").End(xlUp).Row
>> For c = LastRow To 2 Step -1
>>
>> Select Case Left((c, 2), 2)
>>
>> Case Is = "X1": .EntireRow.Delete
>>
>> Next c
>>
>> End Select
>>
>> Application.ScreenUpdating = True
>>
>>
>> End Sub

dim i as long
"c" meant column c

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Little Penny" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Question.
>
> I'm not a vba guru but why does the code work with declaring i as a
> variable, and what is c?
>
> Thanks
>
>
>
>
> On Sat, 19 Jan 2008 14:51:42 -0600, "Don Guillett"
> <(E-Mail Removed)> wrote:
>
>>Sub deleteif()
>> For i = Cells(Rows.count, "c").End(xlUp).Row To 2 Step -1
>> Select Case UCase(Left(Cells(i, "c"), 2))
>> Case Is = "X1": Rows(i).Delete
>> Case Else
>> End Select
>> Next i
>>End Sub
>>
>>
>>
>> --
>>Don Guillett
>>Microsoft MVP Excel
>>SalesAid Software
>>(E-Mail Removed)
>>"Little Penny" <(E-Mail Removed)> wrote in message
>>news:(E-Mail Removed)...
>>>I am trying to get this code to delete every row if the value in
>>> column B begins with "X1".
>>>
>>> I'm getting a syntax error any help would greatly be appreciated
>>>
>>> Error here - Select Case Left((c, 2), 2)
>>>
>>>
>>>
>>> Complete code:
>>>
>>>
>>>
>>> Sub DeleteRowCase()
>>> Dim c As Long
>>> Dim LastRow As Long
>>>
>>> Application.ScreenUpdating = False
>>> LastRow = Range("C65536").End(xlUp).Row
>>> For c = LastRow To 2 Step -1
>>>
>>> Select Case Left((c, 2), 2)
>>>
>>> Case Is = "X1": .EntireRow.Delete
>>>
>>> Next c
>>>
>>> End Select
>>>
>>> Application.ScreenUpdating = True
>>>
>>>
>>> End Sub

I just copied and pasted the code.I just don't understand why the
code worked without dim i as long.


Sub deleteif()
For i = Cells(Rows.count, "c").End(xlUp).Row To 2 Step -1
Select Case UCase(Left(Cells(i, "c"), 2))
Case Is = "X1": Rows(i).Delete
Case Else
End Select
Next i
End Sub





On Sun, 20 Jan 2008 12:11:03 -0600, "Don Guillett"
<(E-Mail Removed)> wrote:

>
>dim i as long
>"c" meant column c

It's always good practice to declare your variables--but you don't absolutely
need to.

If you don't declare the variable, then excel/vba will make it a Variant
--just like if you explicitly did: Dim i as Variant

(I thought you wanted column B???)

Little Penny wrote:
>
> I just copied and pasted the code.I just don't understand why the
> code worked without dim i as long.
>
> Sub deleteif()
> For i = Cells(Rows.count, "c").End(xlUp).Row To 2 Step -1
> Select Case UCase(Left(Cells(i, "c"), 2))
> Case Is = "X1": Rows(i).Delete
> Case Else
> End Select
> Next i
> End Sub
>
> On Sun, 20 Jan 2008 12:11:03 -0600, "Don Guillett"
> <(E-Mail Removed)> wrote:
>
> >
> >dim i as long
> >"c" meant column c

--

Dave Peterson

Thanks


I do want column B. But now I understand the code and changes that I
need to make for any column.

Thanks Again


>It's always good practice to declare your variables--but you don't absolutely
>need to.
>
>If you don't declare the variable, then excel/vba will make it a Variant
>--just like if you explicitly did: Dim i as Variant
>
>(I thought you wanted column B???)
>
>Little Penny wrote:
>>
>> I just copied and pasted the code.I just don't understand why the
>> code worked without dim i as long.
>>
>> Sub deleteif()
>> For i = Cells(Rows.count, "c").End(xlUp).Row To 2 Step -1
>> Select Case UCase(Left(Cells(i, "c"), 2))
>> Case Is = "X1": Rows(i).Delete
>> Case Else
>> End Select
>> Next i
>> End Sub
>>
>> On Sun, 20 Jan 2008 12:11:03 -0600, "Don Guillett"
>> <(E-Mail Removed)> wrote:
>>
>> >
>> >dim i as long
>> >"c" meant column c