Better way to code this code snippet (copy paste)

Question

I am trying to improve upon a VBA code created through recording a macro. Can the code below be optimized (I was informed to avoid .Select as much as possible)? The code itself works fine for me though.

 Sheets("DataExport").Select
 Range("A1").Select
 Range(Selection, Selection.End(xlToRight)).Select
 Range(Selection, Selection.End(xlDown)).Select
 Selection.ClearContents
 Sheets("Analysis").Select
 Range("BX1:CI4").Select
 Selection.Copy
 Sheets("DataExport").Select
 Range("B1").Select
 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
 :=False, Transpose:=False

Answer 1

Try to avoid using Select. You could try the following code:

 Dim ws_data As Worksheet, ws_analysis As Worksheet
 Dim lRow As Long, lCol As Long
 
 Set ws_data = ThisWorkbook.Worksheets("DataExport")
 Set ws_analysis = ThisWorkbook.Worksheets("Analysis")
 With ws_data
 lRow = .Cells(.Rows.Count, 1).End(xlUp).Row
 lCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
 .Range(.Cells(1, 1), .Cells(lRow, lCol)).ClearContents
 ws_analysis.Range("BX1:CI4").Copy
 .Range("B1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
 End With

Answer 2

Let me give you the following answer/advise:

As far as your first piece of code is concerned, this can be re-written as:

Sheets("DataExport").Range("A1").CurrentRegion.ClearContents

As far as the second piece of code is concerned (about the copying), I'd like to refer you to this very complete answer. (I might give you the answer but I believe you'd better try to create it, based on this URL. If you have any issues, please edit your question, we'll have another look).

• Sad 22: Thank you very much, the link provided is indeed informative and gave me the answers.
• Dominique: @Sad22: Your welcome. In case my answer helped you find the answer, please indicate this answer as useful.

Answer 3

Second part:

With Sheets("Analysis").Range("BX1:CI4")
 Sheets("DataExport").Range("B1").Resize(.Rows.count, .Columns.count).Value = .Value
End With