Excel VBA setting cells as a variable

Question

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

I am trying to copy cell B101 and special paste as values to cell N94 then delete the rows 95-101. After that I want to select another cell in Column B and repeat the procedure. — NoNo
– NoNo, Commented Jan 30, 2014 at 9:21

Siddharth Rout · Accepted Answer · 2014-01-30 09:43:09Z

1

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

edited Jan 30, 2014 at 9:43

answered Jan 30, 2014 at 9:32

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

NoNo Over a year ago

That is something like it. But I would like to click on the cell and let the macro run. If I have to change the section like B101 it would take longer than me manually doing it. If that could be a variable I would be most happy :) I have about 6000 rows left and I have been doing it manually.

Siddharth Rout Over a year ago

Change .Range("B101") to ActiveCell :)

NoNo Over a year ago

Almost there: I have Set ws = "ASM-Concatented Address w notes" and when I run it I get an input box to create a table and select my data cell. ~ Unsure what it means.

Siddharth Rout Over a year ago

change Set ws = "ASM-Concatented Address w notes" to Set ws = ThisWorkbook.Sheets("ASM-Concatented Address w notes") I am assuming that you have a sheet with that name in your workbook.

NoNo Over a year ago

Siddharth it is working awesome. Anyway that removing the 7 rows after the pasted cell. Like if the paste goes to n94 the rows 95 through 102 can be deleted.

|

Collectives™ on Stack Overflow

Excel VBA setting cells as a variable

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related