Removing select statements in VBA

SerenityNetworks · #1 04-12-2018, 08:56 AM

I have a very long set of recorded macros that use select statements a lot. Consequently, the performance is very slow. But in trying to get rid of the select statements, there is something I'm doing wrong. I'm not getting the syntax correct. The examples below show three common occurrences. If someone can correct these examples then perhaps I can take it from there.

Thanks in advance,
Andrew

Code:

Range("A6").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1).Select
ActiveCell.FormulaR1C1 = "x"

Code:

ActiveSheet.Shapes.Range(Array("Rounded Rectangle 50")).Select
Selection.ShapeRange.ZOrder msoBringToFront
 Range("A5").Select

Note that when I'm selecting the range, it is dynamic. I am not able to hard code a range as A10:A1000.

Code:

Range("A6").Select
Range(Selection, Selection.End(xlDown)).Select
On Error GoTo NoX
Selection.Find(What:="x", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Select
ActiveCell.ClearContents

p45cal · #2 04-13-2018, 06:33 AM

Code:

Range("A6").End(xlDown).Offset(1).Value = "x"

Code:

ActiveSheet.Shapes("Rounded Rectangle 50").ZOrder msoBringToFront
'Range("A5").Select

Code:

On Error GoTo NoX
Range(Range("A6"), Range("A6").End(xlDown)).Find(What:="x", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False).ClearContents

With the last one, instead of On Error Goto, you could:

Code:

Dim myCell As Range
Set myCell = Range(Range("A6"), Range("A6").End(xlDown)).Find(What:="x", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
If Not myCell Is Nothing Then myCell.ClearContents 'Else something else (such as a msgbox informing the user of non-existence)

SerenityNetworks · #3 04-13-2018, 06:55 AM

Thank you.

On the first one, that's what I had thought and tried. I must have just had a typo'.

On the second one, why is the second line commented out? It looks like in this case that it should be uncommented and I need the select statement. Is that correct?

On the last one, thank you. I'd never have figured that out, but now seeing it I do believe it will help me figure out some others and trim down the code.

Thanks again,
Andrew

p45cal · #4 04-13-2018, 07:13 AM

Quote:

Originally Posted by SerenityNetworks

On the second one, why is the second line commented out? It looks like in this case that it should be uncommented and I need the select statement. Is that correct?

I wasn't sure whether it was part of a recorded macro and so whether you needed it or not

Quote:

Originally Posted by SerenityNetworks

On the last one, thank you. I'd never have figured that out, but now seeing it I do believe it will help me figure out some others and trim down the code.

I added a comment later about this one in my first post in this thread.

SerenityNetworks · #5 04-13-2018, 07:16 AM

This is perfect and makes perfect sense. Thank you very much.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Combined MAX and IF statements?	ddansie	Excel	8	07-05-2017 08:49 AM
Error in If statements	Warren99	Excel	8	05-29-2016 01:59 PM
Help with if then statements	brent chadwick	Word VBA	35	07-25-2015 02:41 PM
Using IF statements	stuwoolf	Excel	2	01-10-2015 01:58 PM
getting IF and OR to work for 3 IF statements	nero6014	Excel	3	09-16-2014 07:28 AM