Copy Data after last row containing data in Col G

[howard]

I have the following code to copy data into Col G

When the data is extracted, I want the code to be amended, so it extracts it after the last row containing data in Col G

Your assistance is most appreciated

 Sub Variance_Extraction()

Dim ws As Worksheet, r As Range, msg As String, ff As String, Sp As Variant
For Each ws In Sheets
Set r = ws.Columns("a").Find("Variance")

If Not r Is Nothing Then
ff = r.Address
Do
         If 0.001 < Abs(Val(CStr(r.Offset(0, 4).Value))) Then

             msg = msg & ws.Name & " " & r.Address(0, 0) & ","
End If
Set r = ws.Columns("a").FindNext(r)
Loop Until ff = r.Address
End If

Next
If Len(msg) = 0 Then
MsgBox "No Variances Found"
Exit Sub
Else
'MsgBox msg
Sp = Split(msg, ",")
Worksheets("Macro").Range("G2").Resize(UBound(Sp) + 1).Value = Application.Transpose(Sp)
End If
Sheets("macro").Select
End Sub

 

[Fluff]

How about

Worksheets("Macro").Range("G"&Rows.count).end(xlUp).Offset(1).Resize(UBound(Sp) + 1).Value

 

[howard]

Hi Fluff

Thanks for the help

Code should be-left out code in bold

 Worksheets("Macro").Range("G"&Rows.count).end(xlUp).Offset(1).Resize(UBound(Sp) + 1).Value  = Application.Transpose(Sp)

 

[Fluff]

Code should be-left out code in bold