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
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