Simple Cut/Paste Marco.. looking for best option

[timbe3]

Hi, All

I'm fairly new to writing macros in VBA and just wanted to see if I am doing this right or if there is a better way to write this. The program is fairly simple, it cuts all data (rows and columns) starting at A13 and pastes it to the next unused row of another sheet.

The macro seems to work fine; however, I plan to incorporate this into a much larger marco and want to make sure that the code is as optimal and error free as possible. Any advice would be greatly appreciated. The code is as follows:

    Sheets("New Data").Activate
    ActiveSheet.Range("A13").Select
    ActiveSheet.Range(Selection, Selection.End(xlToRight)).Select
    ActiveSheet.Range(Selection, Selection.End(xlDown)).Cut
    Sheets("All Data").Select
    Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select
    ActiveSheet.Paste

 

[portews]

After recording a macro, the first thing I do is merge the Select and Selection rows. In VBA, you don't need to select a cell to do anything with it, you just say "Cut this referenced range. Paste it in this referenced cell", rather than "Select this Range. Cut it. Select this cell. Paste here." This makes the code a little more efficient and faster.
Using End(xlUp) and End(xlToLeft) goes to the very extents of the spreadsheet and works backwards makes sure you're catching the last row or column. Using the End(xlDown), etc., you may get stopped at a blank row in the middle and not capture the whole list.

Using the "With Sheets..." & "End With" just saves you a little typing if you're doing a lot on one worksheet (or Range, or whatever). Just remember to put the dot in where you would usually put in the sheet name.

Try this:

Sub test()
Dim lastrow
Dim lastcolumn
With Sheets("New Data")
[COLOR=#00FF00]    'find the last column in the first row[/COLOR]
    lastcolumn = .Cells(1, .Columns.Count).End(xlToLeft).Column
[COLOR=#00ff00]    'finds the last row in the A column[/COLOR]
    lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
[COLOR=#00ff00]    'cuts the range A13:last column, last row[/COLOR]
Sheets("New Data").Range(.Cells(13, 1), .Cells(lastrow, lastcolumn)).Cut
End With


[COLOR=#00ff00]'paste the data in the next free row on All Data[/COLOR]
With Sheets("All Data")
.Paste Destination:=.Cells(.Cells(.Rows.Count, "A").End(xlUp).Row + 1, 1)
End With


End Sub

****** id="cke_pastebin" style="position: absolute; top: 184px; width: 1px; height: 1px; overflow: hidden; left: -1000px;"> 'find the last column in the first row
lastcolumn = .Cells(1, .Columns.Count).End(xlToLeft).Column
</body>