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
Your code is close to finish, you just need to change a code line.
Select
is not necessary in most case.
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