Solved: Deleting Row if word ends in "a" in column A?

[Simon Lloyd]

Hi all! I have posted a solution to someone here http://www.ozgrid.com/forum/showthre...927#post318927 their question was can an entire row be deleted if the word in column A ended in "A"? so this is what i came up with:
[vba]
Sub letterremove()
Dim MyStr
Dim Rng As Range
Set Rng = Range("A1:A500")
On Error Resume Next
Set MyStr = Right(MyStr, 1)
For Each MyStr In Rng
MsgBox "Found " & MyStr
If LCase(Right(MyStr, 1)) = "a" Then
MyStr.Select
With Selection
.EntireRow.Delete
End With
End If
Next
End Sub
[/vba]as you can see its a loop and i thought that if the list in column a was 000's of rows long it would pretty much take forever!, also i had to use On Error Resume Next - i know its not desirable but its the only way i could combat this line

Set MyStr = Right(MyStr, 1)

as of course this gave an error, i just couldnt think how to start at the top of the column working my way down finding words that ended in "a" and deleteing the row!

Anyone got some learning tips for me, suggestions or comments? if you do post back here i would like to post any solutions or suggetions to the other forum too!

Regards,
Simon

[mdmackillop]

Hi Simon
A few pointers


Dim MyStr as ???
For flexibility, Rng should be dynamic, but do you need the Range?
To delete rows in this fashion, you need to start at the bottom.
You’ll need something like "For I = LastRow to 1 Step -1"
It’s not necessary to select a cell in order to delete it.

[Simon Lloyd]

Malcom, thanks for the feedback - the Dim MyStr - i know it was haste - i had a slap on the wrists a few posts ago from Bob for not actually declaring things properly.

When you say the range should be dynamic what do you mean? i do understand the nature of starting at the last row and think that would be a good substitution but does that cope with blanks?, for arguments sake if we say last row but one is blank would that mean that it would start at last but 2? I did try MyStr.EntireRow.Delete but i didnt get a result from that during testing!

If we didnt need the range how do we define where to look for the result of MyStr?

Regards,
SImon

[Bob Phillips]

Never select.
Delete bottom up (unless you save the ranges and delete at the end, or use autofilter).
Declare your variables.
If you are going to test against a variable, initialise it.
Turn screen updating off.

[vba]

Sub letterremove()
Dim iLastRow As Long
Dim i As Long

Application.screenupdating = False
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Dim Rng As Range
For i = iLastRow To 1 Step -1
If LCase(Right(Cells(i, "A").Value, 1)) = "a" Then
Rows(i).Delete
End If
Next i
Application.screenupdating = True
End Sub
[/vba]

[Simon Lloyd]

Thanks Bob - i'll consider myself slapped again! before you posted i was whittling it down to this (doesn't work yet!)
[VBA]
Sub rremove()
Dim MyStr As String
Dim I As Long
Dim LastRow
Set LastRow = Range("A65536").End(xlUp).Offset(1, 0)
For I = LastRow To 1 Step -1
If LCase(I.Right(MyStr, 1)) = "a" Then
I.EntireRow.Delete
End If
Next I
End Sub
[/VBA]I'm trying - very! my mum says!

where it takes you fellows a few moments to come up with a solution it can take me hours - but with the help and experience you all give i'll get better - Promise!

Regards,
Simon

[mdmackillop]

[vba]Sub rremove()
'Not required
Dim MyStr As String
Dim I As Long
Dim LastRow 'as ?
'Here you are setting LastRow to the bottom cell as a Range
Set LastRow = Range("A65536").End(xlUp).Offset(1, 0)
'You can't step through cells in this fashion. Use the Cells
'method as in Bob's code
'Here you're using LastRow as a number, not a Range
For I = LastRow To 1 Step -1
'MyStr has not been set to anything so you'll get no return.
If LCase(I.Right(MyStr, 1)) = "a" Then
I.EntireRow.Delete
End If
Next I
End Sub
[/vba] and always use Option Explicit

[Simon Lloyd]

Thanks for the analysis - it sort of looked the part! LOL

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row

I haven't used this before could you breakdown the elements for me? I thought

Cells

reffered to Every cell on the worksheet, i understand

Rows.count, "A"

as count the rows in column A

.End

to the end but then why do we need

(xlUp)

if we have counted the rows and why the need for

.Row

at the end?

If LCase(Right(Cells(i, "A").Value, 1)) = "a" Then

in this statement i still see the "Cells" as reffering to all cell on the sheet. Probably pretty simple questions to you but i dont really have an understanding of the relationships!

Regards,
Simon

[mdmackillop]

We use Rows.Count because the new Excel will have 2^20 rows. This keeps the code flexible between versions
Cells(Rows.Count,1).End(xlUp) is like going to the bottom of the spreadsheet and pressing Control + Up Arrow.
If you're going to use the Cells notation, you need to find this row number, hence the .Row
Cells(i,"A") uses the value of i so refers to only one cell. If i = 3 this would be A3.
Any clearer?

[Simon Lloyd]

Ok just fiddled with the Cells portion its another way of expressing a range - Cells(1,1).Select is A1!

Regards,
Simon

[Simon Lloyd]

Got it!, just lacking in professional VBA education, most things i have learned have been through trial and error, others have been from forums like this.

For my experience i post quite a few solutions over a few forums, some i like and some i have given are weak - i used to just let someone else pick up where i left off and give a better solution but i feel its not fair to the askers as they are relying on the people who post back for the knowledge, hence questions and postings like this!

Regards,
Simon

[mdmackillop]

Correct, or Cells(1,"A")
BTW you can also use end(xlDown), (xlToLeft), (xlToRight) as appropriate.

[mdmackillop]

Got it!, just lacking in professional VBA education, most things i have learned have been through trial and error, others have been from forums like this.

I, and a few others here, started the same way.

[Zack Barresse]

I would make a couple notes here which have not been mentioned by name, but only by example. You'll almost always find that a loop will go faster when you're using numbers (i.e. the loops Malcolm and Bob suggested) as opposed to looping through an actual range. With ranges, Excel must know everything about the range before doing anything with it, which it has many properties. Using the number as a variable will speed up the process and Excel will not have to interrogate every part of the object (range) while performing the task(s). Hence an efficiency gain.

The other thing not really mentioned is that I generally say one should specify the sheet when working with VBA code. Why? Because when others use the code, they may not know that they have to be on the right sheet for the code to perform correctly. And without an undo feature for VBA code, it can pi$$ a lot of people off. We've all done it, I just feel it is good practice and it covers your bum.

[Bob Phillips]

One other thing to note Simon is that I used

[vba]iLastRow = Cells(Rows.Count,"A").End(xlUp).Row[/vba]

instead of

[vba]iLastRow = Range("A1").End(xlDown).Row[/vba]
as mentioned by Malcolm is because you metioned blank rows, and the first method takes you to the last row, regardless of embedded blank rows or not, whereas the second will take you to the last riow before the FIRST blank, so the embedded blanks dictate where it stops.

BTW, I wasn't slapping you, you're my mate .

[Simon Lloyd]

Zack, thanks for the quick lesson....noted and will try to conform to those although i may still slip.....on many occassions!

Regards,
Simon

[Bob Phillips]

Ok just fiddled with the Cells portion its another way of expressing a range - Cells(1,1).Select is A1!

Regards,
Simon

And so is

Cells(1,"A")

You can also specify a range

Range(Cells(1,"A"),Cells(20,"M"))

[mdmackillop]

...and going back to situations where a dynamic range is required
[VBA]Set rng = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
[/VBA]

[Simon Lloyd]

Thanks Bob, Malcom! i like the way that works, Bob i meant slapped in the nicest sense! (feathers and leather gear not included this time!), where ou specify a range

Range(Cells(1,"A"),Cells(20,"M"))

what benefit does that have over Range("A1:M20)? as the latter takes less time to type

regards,
Simon

[Bob Phillips]

Thanks Bob, Malcom! i like the way that works, Bob i meant slapped in the nicest sense! (feathers and leather gear not included this time!), where ou specify a range what benefit does that have over Range("A1:M20)? as the latter takes less time to type

In this instance it makes no difference, but in VBA you are more likely to be dealing wither with variable ranges, where you have to work out the start position and/or the size, in which case the power comes into its own. We had a discussion a while back about using Cells against Rangem, because just as you can use

[vba]

Cells(iLastRow,"A")
[/vba]

you can just as legitimately use

[vba]

Range("A" & iLastRow)
[/vba]

Myself and a few others (the smarter developers ) stated a preference for the Cells method, others preferred Range, some were not fussed . I just find Cells more flexible and easier to work with, especially combined with Resize.

BTW, I would never use

[vba]

Range("A1:M20")
[/vba]

in (my own) code, I would always define a name and use that. Again we had a discussion about it a while back in this thread. Some there were claiming names are a bad idea. They are just plain wrong, as with all things there are some situations where they can add to problems, but a) you need to be aware of these areas and design accordingly, and b) it is usually a sign of a bigger underlying problem. Names are useful, and should be practiced by all serious developers.

[Bob Phillips]

...and going back to situations where a dynamic range is required
[vba]Set rng = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
[/vba]

Following on from Simon's follow-up and my response, I wouldn't do it that way, it is clumsy and not intuitive IMO. I would use

[vba]
With Ws 'see Zack, I do listen to you ... sometimes
iLastRow = .Cells(.Rows.Count, .Range("startCell").Column).End(xlUp)).Row
Set rng = .Range("startCell").Resize(iLastRow - .Range("startCell").Row + 1)
End With
[/vba]
Note it is nearly totally dynamic.