Can this VBA code be written better?

[kalim]

Hi excel users.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I was wondering if anyone could look at this VBA code to see if it can be done more efficiently (not good with VBA) Thanks. In case you are wondering "b2" has a value that needs to be cleared first.

Sub list_1()
 
Sheet1.Activate
 
    Range("B2").ClearContents
    Range("f2:f41").Select
    Selection.Copy
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Range("A42:A53").Select
    Selection.ClearContents
    Sheet3.Select
    Range("B1").Select
 
End Sub

 

[Colin Legg]

Hi,

You can tidy it up by getting rid of the unnecessary Active/Activate/Select/Selection bits.

Sub list_1()
 
    Sheet1.Range("B2").ClearContents
    
    With Sheet1.Range("f2:f41")
        Sheet1.Range("A2").Resize(.Rows.Count, .Columns.Count).Value = .Value
    End With
    
    Sheet1.Range("A42:A53").ClearContents
    
End Sub

 

[VoG]

Hi. Try this

Sub list_1()
 
Sheet1.Activate
 
    Range("B2").ClearContents
    Range("f2:f41").Copy
    Range("A2").PasteSpecial Paste:=xlPasteValues
    Range("A42:A53").ClearContents
    Sheet3.Select
    Range("B1").Select
 
End Sub

 

[Boller]

With Sheet1
    .[B2].ClearContents
    .[A2:A31] = .[F2:F31].Value
    .[A42:A53].ClearContents
End With
Sheet3.Select
[B1].Select

 

[snb_]

This suffices:

With Sheet1
  .[A2:A31] = .[F2:F31].Value
  .[B2,A42:A53].ClearContents
End With

 

[Boller]

This suffices:

With Sheet1
  .[A2:A31] = .[F2:F31].Value
  .[B2,A42:A53].ClearContents
End With

Yes, that's more concise except you've missed off :-

Sheet3.Select
[B1].Select

 

[Colin Legg]

Using the Range property is better than using Evaluate []. It benefits from early binding, thus you get intellisense and it is more efficient. It also (IMO) makes the code clearer to read.

 

[GTO]

Certainly just my opinion, but Evaluate is not what I would use for this. Regardless of how handy the help topic makes is sound, Evaluate has to, well, evaluate what it's supposed to be doing and return an object or a value. I would suggest similar to Colin's.

Sub list_1()
    
    With Sheet1
        .Range("B2").ClearContents
        .Range("A2:A41").Value = .Range("f2:f41").Value
        .Range("A42:A53").ClearContents
    End With
    Application.Goto Sheet3.Range("B1").Select
    
End Sub

This way you maintain good explicit references, and keep intellisense helping you along the way.

Mark

 

[GTO]

ACK! Sorry Colin, slow typist here...

 

[JackDanIce]

For what it's worth, depending on what you mean by better (Easier to read? Faster execution? Less code?), a tiny modification to Mark's suggestion (by no means am I trying to be pedantic, apologies in advance!) might be:

Sub list_1()
    
    With Sheet1
        .Range("A42:A53,B2").ClearContents
        .Range("A2:A41").Value = .Range("F2:F41").Value
    End With
    Application.Goto Sheet3.Range("B1").Select
    
End Sub

Ok, it's only reducing the number of lines of code by 1, but it really depends on what you mean by "better" (IMO)

Jack