VBA single col select & copy (from last cell to last cell with data) gives "Method 'range' of object _Global... "

Question

The purpose of my code is to:

Clean the two destination column
At source worksheet jump to last cell(1048576) in a specific column from bottom, jump to the last cell with data, and select from there to the top cell
copy, paste, and remove duplicates (the removeduplicates is a working part)

I have to select the range from bottom because of embedded empty cells that prevents excel to select further cells with data.

The line that performs selection seems correct after multiple checks, and also tried to

use string form ("AC") for parameter
number form (13)
put the cell number in a Range()

Despite all my efforts the line gives an "Run-time error '1004' : Method 'Range' of object'_Global' failed" error.

I removed even all other params except for the, single cell reference, tried to rearrange my code and solve by some other way.

The code part

 Sheets("Data").Cells(Rows.Count, "AC").End(xlUp).Row

returns the value(row number) of the first cell with data from bottom. That would be the end of the selection. I know that there are some parts that doesn't match the description, but they are also irrelevant in terms of the error. (for example at line 17-18, it selects the entire column, but I can fix that later.)

Here is my code, I put a comment next to the problematic part.

Sub CopyUniqueProcList()
Dim ContainWord As String
Dim SrcSheet As Worksheet
Dim DestSheet As Worksheet
Dim TypeRng As Range
Dim TypeRngDest As Range
Dim GrundRng As Range
Dim GrundRngDest As Range
Dim TRD_E As Integer
Dim GRD_E As Integer
    Set SrcSheet = Worksheets("Data")
    Set DestSheet = Worksheets("lTopTenHelper")
        Application.ScreenUpdating = False
        Application.Calculation = xlCalculationManual

    Set TypeRng = SrcSheet.Range("AC4")
    Set TypeRngDest = DestSheet.Range("A1")
    Set TRD_E = DestSheet.Cells(Rows.Count, "A")
    Set GrundRng = SrcSheet.Range("AE4")
    Set GrundRngDest = DestSheet.Range("D1")
    Set GRD_E = DestSheet.Cells(Rows.Count, "D")
     TRD_E = DestSheet.Cells(DestSheet.Rows.Count, "A").End(xlUp).Row
     GRD_E = DestSheet.Cells(DestSheet.Rows.Count, "D").End(xlUp).Row
        Sheets("lTopTenHelper").Range(TypeRngDest, "A" & TRD_E).Clear
        Sheets("lTopTenHelper").Range(GrundRngDest, "D" & GRD_E).Clear



            If TypeRng.Value <> 0 Then
                Range(TypeRng, "AC" & Sheets("Data").Cells(Rows.Count, "AC").End(xlUp).Row).Copy 'Error thrown here
                    Sheets("lTopTenHelper").Cells(Rows.Count, "A").End(xlUp).Offset(0).PasteSpecial Paste:=xlPasteValues
            End If
                    Sheets("lTopTenHelper").Range(TypeRngDest, "AC" & Sheets("Data").Cells(Rows.Count, "AC").End(xlUp).Row).RemoveDuplicates Columns:=Array(1), Header:=xlNo
            If GrundRng.Value <> 0 Then
                Range(GrundRng, "AE" & Sheets("Data").Cells(Rows.Count, "AE").End(xlUp).Row).Copy
                    Sheets("lTopTenHelper").Cells(Rows.Count, "D").End(xlUp).Offset(0).PasteSpecial Paste:=xlPasteValues
            End If                                                                                                                                                '6?
                    Sheets("lTopTenHelper").Range(GrundRngDest, "AE" & Sheets("Data").Cells(Rows.Count, "AE").End(xlUp).Row).RemoveDuplicates Columns:=Array(1), Header:=xlNo

            Application.Calculation = xlCalculationAutomatic

    Exit Sub
End Sub

Any help/suggestion/other solution of task is welcome. Thanks in advance.

I am not getting any errors at that line, can you post the section where you declare all your variables ? where is `TRD_E` , `GRD_E` , `TypeRng` and all other Ranges declared ? — Shai Rado, Sep 29 '16 at 07:13
That is the top section, just added TypeRng, etc... doesn't seems to solve it. — mrNullpointer, Sep 29 '16 at 07:22
Declared as integer, but didn't solved anything, so haven't added to the post. The problem is around the _"Range(TypeRng, "AC" & Sheets("Data").Cells(Rows.Count, "AC").End(xlUp).Row).Copy"_ part, also, I'm not certain about theconcatenating part with the & mark. — mrNullpointer, Sep 29 '16 at 07:27
actualy, if TRD_E is Integer, which is suppose to get the last row with data, then you should use `TRD_E = DestSheet.Cells(DestSheet.Rows.Count, "A").End(xlUp).Row` , the same goes for `GRD_E` — Shai Rado, Sep 29 '16 at 07:31
Fixed that part, surprisingly what works here doesn't works few lines after when using range params the same way -> Range(TypeRngDest, "A" & TRD_E) — mrNullpointer, Sep 29 '16 at 07:47
is `TRD_E` suppose to be the last row with data in Column A ? or Column AC? — Shai Rado, Sep 29 '16 at 09:05
Possible duplicate of [Run-time error '1004' : Method 'Range' of object'\_Global' failed](http://stackoverflow.com/questions/12174723/run-time-error-1004-method-range-of-object-global-failed) — Comintern, Sep 29 '16 at 14:58

score 0 · Answer 1 · answered Sep 29 '16 at 09:27

The code below will get you started,

Option Explicit

Sub CopyUniqueProcList()

Dim ContainWord             As String
Dim SrcSheet                As Worksheet
Dim DestSheet               As Worksheet
Dim TypeRng                 As Range
Dim TypeRngDest             As Range
Dim GrundRng                As Range
Dim GrundRngDest            As Range
Dim TRD_E                   As Long
Dim GRD_E                   As Long

Set SrcSheet = Worksheets("Data")
Set DestSheet = Worksheets("lTopTenHelper")

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Set TypeRng = SrcSheet.Range("AC4")
Set GrundRng = SrcSheet.Range("AE4")

With DestSheet

    ' find last row with data in Column A
    TRD_E = .Cells(.Rows.Count, "A").End(xlUp).Row
    GRD_E = .Cells(.Rows.Count, "D").End(xlUp).Row

    Set TypeRngDest = .Range("A1:A" & TRD_E)
    Set GrundRngDest = .Range("D1:D" & GRD_E)

    TypeRngDest.Clear
    GrundRngDest.Clear
End With

If TypeRng.Value <> 0 Then
    SrcSheet.Range("AC4:AC" & SrcSheet.Cells(SrcSheet.Rows.Count, "AC").End(xlUp).Row).Copy
    DestSheet.Cells(TRD_E, "A").Offset(1, 0).PasteSpecial Paste:=xlPasteValues
End If

' you can take it from here ... ?

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

Exit Sub

End Sub

VBA single col select & copy (from last cell to last cell with data) gives "Method 'range' of object _Global... "

1 Answers1