CODEWITHSUNDEEP
excelvba
Seb KB2
Seb KB2

Reputation: 13

Using Range.End without using .select

I do not know if this is possible, but I hereby call upon you VBA masters for some help.

To summarize: I am appending a formula to a table via VBA, which populates the entire column, and then attempting to copy and paste values to the same column,(It varies in size, hence why the .End(XlDown) (Ideally without using select as it slows the VBA down))

I have no issue using:

Sheets("Cancelations Temp").Range("P2", Range("P2").End(xlDown)).Copy
Sheets("Cancelations Temp").Range("P2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

But, by using this, I need to have the sheet ("Cancelations Temp") selected, if it is not, I get an application-defined error.

Is there a way for me to basically achieve this (copying a column that changes sizes on each run of the macro), without using a select method?

I supposed I could see where the range end each time, define it as a variable (Say Var), and then do it via: 

Sheets("SheetName").Range("P2:Var").Copy
Sheets("SheetName").Range("P2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

I hope I haven't messed up the formating... I probably have though, will try and fix once I see how it came out

This is my current code:

Sheets("Cancelations Temp").Range("P2").FormulaR1C1 = _
        "=IFERROR(IF(VLOOKUP([@[Policy Number]],Temp_Cancelations,6,FALSE)=""MTC"",""Canceled"",IF(VLOOKUP([@[Policy Number]],Temp_Cancelations,6,FALSE)=""MTR"",""Reinstated"",""Not Canceled"")),"""")"
    'Sheets("Cancelations Temp").Select
    Sheets("Cancelations Temp").Range("P2", Range("P2").End(xlDown)).Copy
    Sheets("Cancelations Temp").Range("P2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

When run within the Sheet mentioned, I get no issues and all is correct. When I run it from the main page (Where the user would run the macro from), I get an application or object defined error. When I run it with the Select method (Commented in the code above), it runs fine, but goes to the sheet in question.

Thanks in advance, I have searched around for this, but no resolution was found.

Upvotes: 0

Views: 947

Answers (2)

Gary's Student
Gary's Student

Reputation: 96753

This uses your suggested approach:

Sub KopyPaste()
    Dim N As Long, rng As Range
    With Sheets("Cancelations Temp")
        N = .Cells(Rows.Count, "P").End(xlUp).Row
        Set rng = .Range("P2:P" & N)
        rng.Copy
        rng.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    End With
End Sub

As an alternative to Copy / PasteSpecial:

Sub KopyPaste()
    Dim N As Long, rng As Range
    With Sheets("Cancelations Temp")
        N = .Cells(Rows.Count, "P").End(xlUp).Row
        Set rng = .Range("P2:P" & N)
        rng.Value = rng.Value
    End With
End Sub

Upvotes: 1

Damian
Damian

Reputation: 5174

Here you go, to only turn the range you need:

Option Explicit
Sub Test()

    Dim LastRow As Long

    With ThisWorkbook.Sheets("Cancelations Temp") 'using this you avoid the need to reference it again between the With
        .Range("P2").FormulaR1C1 = _
            "=IFERROR(IF(VLOOKUP([@[Policy Number]],Temp_Cancelations,6,FALSE)=""MTC"",""Canceled"",IF(VLOOKUP([@[Policy Number]],Temp_Cancelations,6,FALSE)=""MTR"",""Reinstated"",""Not Canceled"")),"""")"
        LastRow = .Cells(.Rows.Count, "P").End(xlUp).Row 'easy way to calculate the last row
        .Range("P2", .Cells(LastRow, "P")).Value = .Range("P2", .Cells(LastRow, "P")).Value
    End With

End Sub

I'm assuming the workbook you are working is the one holding the code, hence the ThisWorkbook.

Upvotes: 1

Related Questions