CODEWITHSUNDEEP
vbaexcel
MG78
MG78

Reputation: 219

Excel VBA - Copy Ranges to Non Empty Rows

Im looking on how to copy ranges of data until non blank/empty rows. For example i have list of data on column A until G. I want to copy data on those ranges until the last rows. I have code that can find the last row as below:

    Sheets("REFS").Range("E1").End(xlDown).Select
    Rows(Selection.Row).Select
    Selection.Copy

    Sheets("TP1").Range("A2").PasteSpecial xlPasteValues

Upvotes: 0

Views: 703

Answers (1)

Moacir
Moacir

Reputation: 627

Here it is. I tested here and it seems to work

Sub CopyAllRows()
   Dim j As Long

   Dim refs As Worksheet
   Set refs = Sheets("REFS")

   Dim tp1 As Worksheet
   Set tp1 = Sheets("TP1")

   Dim src As Variant

   j = refs.Range("E1").End(xlDown).Row
   src = refs.Range("A1:G" & j).Value
   tp1.Range("A2:G" & j + 1).Value = src

End Sub

Upvotes: 2

Related Questions