VBA xlUp does not select the range correctly

Question

I am trying to copy a range of values from column L i.e. from L2 until the last cell (supposed to vary each time) that contains a value. Once I succeed with column L, I would like to repeat this action for many other columns. Since column L has some blank cells in between, I have used xlUp. The problem is that the selection does not stop at the last cell with value, but goes until the row number 2542. This time the last cell should have been 542. All the rows in between 542 and 2542 seem to be blank- I even tried deleting and clearing those rows to make sure that there is really nothing in them. But that doesn't seem to help. Below is the code I tried.

Any help is greatly appreciated.

Call CopyRangeAndPasteAt("L2", "AE2", True)

Function CopyRangeAndPasteAt(SrcRange As String, DestIdx As String, Trans As Boolean)
    
    Sheet2.Select
    LastRow = Sheet2.Range("L" & Rows.Count).End(xlUp).Row

    Range(Range(SrcRange), Range(SrcRange & LastRow)).Select
    Selection.Copy
    Sheet6.Select
    Range(DestIdx).Select
    Selection.PasteSpecial Paste:=xlPasteValues  

End Function

Among other issues, you are using `SrcRange & LastRow` instead of e.g. `Left(SrcRange, 1) & LastRow` so it becomes `L2542` instead of `L542`. — VBasic2008, May 29 '21 at 08:41
@VBasic2008 That change did work!! Thanks so much!! Could you please help me understand as to what does `Left(SrcRange, 1)` mean and what does it do? — Seema, May 29 '21 at 09:33
It gets the first character in a string, which is `L` for the string `L2`. It is inaccurate i.e. if the column was `AL` it would fail. A shorter and more accurate way to write the complete line would be `Range(SrcRange, Cells(LastRow, Range(SrcRange).Column)).Select`. — VBasic2008, May 29 '21 at 10:12
@VBasic2008 Thank you very much! Helped me a lot! I really appreciate. — Seema, May 29 '21 at 12:00
Also Seema you may wat to read up on [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Siddharth Rout, May 30 '21 at 05:46

score 1 · Answer 1 · answered May 29 '21 at 10:26

Copy Non-Empty Range to Another Worksheet

It should be a Sub procedure because it doesn't return a value. A Function does.
Use Option Explicit.
Pasting values is faster using 'copying by assignment' e.g. drg.Value = srg.Value.
Avoid using Select and Active.
Since you're using strings for the arguments you should use error handling. See it in action when using an invalid address, e.g. "A".
The first argument should be a one-row (one-cell) address, while the second should be a one-cell address.
The code may be out of your league, but that shouldn't stop you from using it successfully. The first procedure illustrates how you can utilize it easily.

Option Explicit

Sub CopyRangeAndPasteAtTEST()
    CopyRangeAndPasteAt "L2", "AE2"
    'CopyRangeAndPasteAt "L2", "AE2", True ' will transpose
    'CopyRangeAndPasteAt "L2:O2", "AE2" ' more columns (more rows not included)
    'CopyRangeAndPasteAt "L2", "AE2", True ' will transpose (more columns)
End Sub

Sub CopyRangeAndPasteAt( _
        ByVal SourceAddress As String, _
        ByVal DestinationAddress As String, _
        Optional ByVal doTranspose As Boolean = False)
    Const ProcName As String = "CopyRangeAndPasteAt"
    On Error GoTo clearError

    Dim sws As Worksheet: Set sws = Sheet2
    Dim dws As Worksheet: Set dws = Sheet6
    
    Dim srg As Range
    Dim rCount As Long, cCount As Long
    Dim wasCopied As Boolean
    
    With sws.Range(SourceAddress).Rows(1)
        cCount = .Columns.Count
        Dim lCell As Range
        Set lCell = .Resize(.Worksheet.Rows.Count - .Row + 1) _
            .Find("*", , xlFormulas, , xlByRows, xlPrevious)
        If lCell Is Nothing Then GoTo ProcExit
        rCount = lCell.Row - .Row + 1
        Set srg = .Resize(rCount)
    End With
    
    Dim drg As Range
    With dws.Range(DestinationAddress).Cells(1)
        If doTranspose Then
            Dim sData As Variant
            If cCount + rCount = 2 Then
                ReDim sData(1 To 1, 1 To 1): sData(1, 1) = srg.Value
            Else
                sData = srg.Value
            End If
            Dim dData As Variant: ReDim dData(1 To cCount, 1 To rCount)
            Dim r As Long, c As Long
            For r = 1 To rCount
                For c = 1 To cCount
                    dData(c, r) = sData(r, c)
                Next c
            Next r
            Set drg = .Resize(cCount, rCount)
            drg.Value = dData
            ' Clear contents to the right.
            '.Resize(cCount, .Worksheet.Columns.Count - .Column - rCount + 1) _
                .Offset(, rCount).ClearContents
        Else
            Set drg = .Resize(rCount, cCount)
            drg.Value = srg.Value
            ' Clear contents to the bottom.
            '.Resize(.Worksheet.Rows.Count - .Row - rCount + 1, cCount) _
                .Offset(rCount).ClearContents
        End If
    End With
    
    wasCopied = True
    
ProcExit:
    
    If wasCopied Then
        MsgBox "Data successfully copied.", vbInformation, "Copy Range"
    Else
        MsgBox "Data NOT copied.", vbCritical, "Copy Range"
    End If
    
    Exit Sub

clearError:
    Debug.Print "'" & ProcName & "': Unexpected Error!" & vbLf _
              & "    " & "Run-time error '" & Err.Number & "':" & vbLf _
              & "        " & Err.Description
    Resume ProcExit
End Sub

VBA xlUp does not select the range correctly

1 Answers1

Copy Non-Empty Range to Another Worksheet