excelvba

Copy the entire range in VBA (values only)


enter image description here

I'm using the below code to copy certain cells in a worksheet. Now I want to implement the same code to another sheet but the issue is the code only select the cells with values only. instead I want it to select the entire range with data (illustrrated in the image)

New to VB, any help would be much appreciated.

Sub Copy1()
    Dim rng As Range, r As Range, rSel As Range

    Set rng = Range("B8:GS56")
    Set rSel = Nothing

    For Each r In rng
        If r.Value <> "" Then
            If rSel Is Nothing Then
                Set rSel = r
            Else
                Set rSel = Union(rSel, r)
            End If
        End If
    Next r
    If Not rSel Is Nothing Then rSel.Select
Selection.Copy
End Sub

Tried a code failed to get expected result


Solution

  • How to avoid using Select in Excel VBA

    Sub Copy1()
        Dim rng As Range, r As Range, rSel As Range
        
        Set rng = Range("B8:GS56")
        Set rSel = Nothing
        
        For Each r In rng
            If r.Value <> "" Then
                If rSel Is Nothing Then
                    Set rSel = r
                Else
                    Set rSel = Range(rSel, r) ' **
                End If
            End If
        Next r
        If Not rSel Is Nothing Then
            rSel.Select
            Selection.Copy
            ' Copy to somewhere
    '        rSel.Copy Sheets(2).Range("B100")
        End If
    End Sub