Macro, increment the row number

Question

I am using an Excel macro to cut and paste some data and add it to the end of a list. At the moment it is overwriting the last entry.

'macro                 
Range("A3:R93").Select                         
Selection.Copy                         
Range("T100000").Select                        
Selection.End(xlUp).Select  
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

This will take me to the last existing entry in column T.

I wish to paste my new data AFTER this so need to increment my current row by 1

Is there an easy way of doing that?

score 2 · Answer 1 · edited May 23 '17 at 12:07

You do not need to select a cell or cell range in order to copy or paste; in fact, it is better to avoid the practise.

Range("A3:R93").Copy _
  Destination:=Range("T" & Rows.Count).End(xlUp).Offset(1, 0)

For values only:

With Range("A3:R93")
    Range("T" & Rows.Count).End(xlUp).Offset(1, 0).Resize(.Rows.Count, .Columns.Count) = .Value
End With

This locates the last used cell in column T and then uses the Range.Offset property to shift the target of the paste 1 row down and 0 columns across.

If the source or destination area of the copy/paste may contain merged cells, you will need to .UnMerge them first. This is particularly important around the edges of the copy/paste areas where a merged cell may be half in and half out of the source or destination.

Dim rng As Range
Set rng = Range("A3:R93")
rng.UnMerge
With Range("T" & Rows.Count).End(xlUp).Offset(1, 0).Resize(rng.Rows.Count, rng.Columns.Count)
    .UnMerge
    rng.Copy Destination:=.Cells
End With

See How to avoid using Select in Excel VBA macros for more methods on getting away from relying on select and activate to accomplish your goals.

@Bob - I've added some [Range.UnMerge method](https://msdn.microsoft.com/en-us/library/bb210014%28v=office.12%29.aspx) code just in case. — , Nov 07 '15 at 10:13
That all worked a treat, both of them actually since I retried the first bit of code on a new spreadsheet — , Nov 08 '15 at 10:46
In regard to the first example, How would you do it so that it pastes it as values rather than as formulas? — , Nov 08 '15 at 11:37
Added value transfer method. This is the preferred method as it does not involve the clipboard. — , Nov 08 '15 at 11:56

score 0 · Answer 2 · 2015-11-09T05:42:12.867

This is what I now have, works fine, thank you Jeeped

Sub Report5()
'
' Create Report
'
'
Range("DA3:DC10000").Clear

If Range("aw4") Then
    With Range("BB3:BD9")
        Range("DA" & Rows.Count).End(xlUp).Offset(1, 0).Resize(.Rows.Count, .Columns.Count) = .Value
    End With
End If

If Range("aw5") Then
    With Range("BB10:BD23")
        Range("DA" & Rows.Count).End(xlUp).Offset(1, 0).Resize(.Rows.Count, .Columns.Count) = .Value
    End With
End If

etc for another 50 or so catagories

Macro, increment the row number

2 Answers2