Excel VBA settings cells as a variable

I have read articles that have led me to change the first line into a variable but I need the "n94" to be a variable as well. It will always be 7 lines up and on the N column. I have to re-edit this in the future to 6 lines up etc... Trying to concatenate memo notes and its taking way too long with the amount of data.

Range("B101").Select
Selection.Copy
Range("N94").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Rows("95:101").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp

Thank you for any help

Answers


It will always be 7 lines up and on the N column

Is this what you are trying?

Sub Sample()
    Dim ws As Worksheet
    Dim rng As Range

    '~~> Replace this with the actual sheet name
    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        Set rng = ActiveCell '<~~ From Comments below

        If (rng.Row - 7) < 1 Then
            MsgBox "Cannot Paste. Row Out of bounds"
            Exit Sub
        End If

        rng.Copy

        .Cells(rng.Row - 7, "N").PasteSpecial _
        Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

        '~~> Delete the rows. For example Rows("95:101")
        .Rows((rng.Row - 6) & ":" & rng.Row).Delete Shift:=xlUp
    End With
End Sub

Need Your Help

How to Draw Arc In windows phone 7 in canvas in Runtime?

c# windows-phone-7 silverlight-4.0

The Challenge is that i have to make a canvas object in runtime for windows phone 7. And then Add a arc on it , with a startpoint and one end Point . i have tried this code