PDA

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



Simon Lloyd
12-16-2006, 09:35 AM
Hi all! I have posted a solution to someone here http://www.ozgrid.com/forum/showthread.php?p=318927#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:

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
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
12-16-2006, 09:59 AM
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
12-16-2006, 10:34 AM
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
12-16-2006, 10:50 AM
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.



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

Simon Lloyd
12-16-2006, 10:56 AM
Thanks Bob - i'll consider myself slapped again! before you posted i was whittling it down to this (doesn't work yet!)

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
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
12-16-2006, 11:11 AM
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
and always use Option Explicit

Simon Lloyd
12-16-2006, 11:24 AM
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
.Endto the end but then why do we need
(xlUp) if we have counted the rows and why the need for
.Rowat 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
12-16-2006, 11:34 AM
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
12-16-2006, 11:35 AM
Ok just fiddled with the Cells portion its another way of expressing a range - Cells(1,1).Select is A1!

Regards,
Simon

Simon Lloyd
12-16-2006, 11:42 AM
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
12-16-2006, 11:47 AM
Correct, or Cells(1,"A")
BTW you can also use end(xlDown), (xlToLeft), (xlToRight) as appropriate.

mdmackillop
12-16-2006, 11:56 AM
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
12-16-2006, 12:53 PM
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
12-16-2006, 02:54 PM
One other thing to note Simon is that I used

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

instead of

iLastRow = Range("A1").End(xlDown).Row
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
12-16-2006, 02:57 PM
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
12-16-2006, 02:57 PM
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
12-16-2006, 03:01 PM
...and going back to situations where a dynamic range is required
Set rng = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))

Simon Lloyd
12-17-2006, 06:29 AM
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
12-17-2006, 06:40 AM
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



Cells(iLastRow,"A")


you can just as legitimately use



Range("A" & iLastRow)


Myself and a few others (the smarter developers :devil2:) 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



Range("A1:M20")


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 (http://www.vbaexpress.com/forum/showthread.php?t=3083&highlight=name+defined) 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
12-17-2006, 06:43 AM
...and going back to situations where a dynamic range is required
Set rng = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))

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


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

Note it is nearly totally dynamic.

mdmackillop
12-17-2006, 07:23 AM
it is clumsy and not intuitive IMO. I would use


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


:rotflmao:

Zack Barresse
12-17-2006, 02:15 PM
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


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

Note it is nearly totally dynamic.
ROFLMFAO!!!! :biglaugh:

Proof!! :D

Simon Lloyd
12-18-2006, 01:52 AM
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

Bob there's nothing like being direct!, i'm going to mark this solved as i think the lesson is now getting way oer my head - but i have a funny feeling this thread has some mileage left in it!

Thanks to all of you.

Regards,
Simon

Simon Lloyd
12-18-2006, 02:05 AM
Just a quick backtrack question!

in the statements below Bob quite neatly pointed out why he used Cell & Count to take care of embedded blanks - i hae found that if i enter something in a cell and then later delete it Excel still believes that this is the last used cell so the xlUp would start here or indeed as Malcom had a few posts ago he had blanks that weren't/were blank because of an import from Access is there a way to account for this - what i mean s to count cells that have data but not truly blank - i dont think i quite explained myself properly but you get the gist! - I hope







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

VBA tags courtesy of www.thecodenet.com (http://www.thecodenet.com/)


instead of





VBA:
iLastRow = Range("A1").End(xlDown).Row

VBA tags courtesy of www.thecodenet.com (http://www.thecodenet.com/)


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.




Regards,
Simon

Bob Phillips
12-18-2006, 02:27 AM
Simon,

I am not sure I understand. If you want to count cells that are blank there are many ways, SpecialCells(xlCellTypBlank) for instance, but I am not sure that is what you are asking.

Approaching the last cell from the bottom up, rather than from the top down, doesn't regard any embedded cells.

Simon Lloyd
12-18-2006, 08:51 AM
I know Bob very poorly explained by me - its just in the past i have used a cell - deleted the data and excel will always see that as a used cell, i guess what i am picking at is if the cell is way down the rows would this slow excel down? does it still see it as a cell with data in it? and would that interfere with any data we are manipulating or the range when finding the range?

Malcom had a problem here http://vbaexpress.com/forum/showthread.php?t=10322 with imported cells that were blank but not recognised as such in excel so SpecialCells did not work for him - would these kind of cells cause a problem with our Cells method?

Regards,
Simon

Zack Barresse
12-18-2006, 08:51 AM
The only thing the Rows.Count method will *fail* on is null values (i.e. formulas which return ""). There isn't much you can do for that other than try to use a loop. This is why I try not to return formulas to "" (i.e. an IF function). But there is a difference between blank (Empty) and null.

As far as SpecialCells finding the last cell, that method can be dangerous as it can give erroneous results. This is also tied to AutoSave feature. The best way to get the last cell (even better than Rows.Count, IMHO) is the Cells.Find("*") method. This will work no matter how much data is in a column.

Simon Lloyd
12-18-2006, 09:16 AM
Where would that come in using Bob's Almost Completely Dynamic

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
Regards,
Simon

Zack Barresse
12-18-2006, 09:32 AM
It would change this line..

iLastRow = .Cells(.Rows.Count, .Range("startCell").Column).End(xlUp)).Row

.. to this ..

iLastRow = .Cells.Find(what:="*", after:=.cells(1, 1), searchorder:=xlbyrows, searchdirection:=xlprevious).row

The same can be used for the last column as well..

iLastCol = .Cells.Find(what:="*", after:=.cells(1, 1), searchorder:=xlbycolumns, searchdirection:=xlprevious).column

HTH

Simon Lloyd
12-18-2006, 09:59 AM
Yes Zack thanks! i only really asked the question out of pure "Bloody Mindedness" as i have had trouble in the past but i know now that was because i was searching or performing the task from the top of the sheet - but the issues with blank cells that have been previously used i saw as a problem with things i would like to do.....Again thanks to all that have been involved in this thread!

Kind regards,
Simon