Excel VBA: Simplify long Code

1
votes

is it possible to simplify the following code ? Is there any way to make this shorter: 

    ' Paste last value
    Range(Cells(LastRowP + 1, 10), Cells(LastRowP + 1, 10)).Select
    Selection.Cut
    Range(Cells(LastRowP + 1, 9), Cells(LastRowP + 1, 9)).Select
    ActiveSheet.Paste
    Range(Cells(LastRowP + 2, 10), Cells(LastRowP + 2, 10)).Select
    Selection.Cut
    Range(Cells(LastRowP + 2, 9), Cells(LastRowP + 2, 9)).Select
    ActiveSheet.Paste
    Range(Cells(LastRowP + 3, 10), Cells(LastRowP + 3, 10)).Select
    Selection.Cut
    Range(Cells(LastRowP + 3, 9), Cells(LastRowP + 3, 9)).Select
    ActiveSheet.Paste

and this goes on until +20. I'm new to VBA and coding,so please bear with me :)

3
vbaexcel

3 Answers

5
votes
Range(Cells(LastRowP + 1, 10), Cells(LastRowP + 20, 10)).Cut _
          Cells(LastRowP + 1, 9)

or

 Cells(LastRowP + 1, 10).Resize(20,1).Cut Cells(LastRowP + 1, 9)
2
votes
For i = 1 To 20
    Range(Cells(LastRowP + i, 10), Cells(LastRowP + i, 10)).Cut
    Range(Cells(LastRowP + i, 9), Cells(LastRowP + i, 9)).Select
    ActiveSheet.Paste
Next
1
votes

Generally you want to avoid using Select and Selection statements. They have their place but it is rare. You are much better off skipping that step and instead jsut doing what you want.

Dim yourSheet As Worksheet
Dim offset As Long, maxOffset As Long, lastRowP As Long
Dim source As Range, destination As Range

'you always want to be explicit about what sheet you are using
'when you are not explicit unexplected things can happen
Set yourSheet = Sheets("yourSheetName")

maxOffset = 20 ' or however many times you need to do this loop
lastRowP = 10 ' or however you are setting this value
With yourSheet  'starting any statment with a . inside the With will reference this object
    For offset = 0 To maxOffset
        Set source = .Range(.Cells(lastRowP + offset, 10), .Cells(lastRowP + offset, 10))
        Set destination = .Range(.Cells(lastRowP + offset, 9), .Cells(lastRowP + offset, 9))
        source.Cut destination
    Next offset

End With