VBA code to copy rows in range - if cells in first column are not blank

[GreenMan37]

Hi folks,

I'm trying to perform a function that is probably a walk in the park for many of you but it's causing me all manner of grief.
Here's my scenario and I really hope someone is able to assist with a useful code solution.

I have a specific range (C14:AE78) but only some of the rows in that range will be populated. Within the actual row, there will also be blanks in cells in some columns. What I would like to do is copy only those rows within the stated range that have values in Column C. In other words, if cells in column C (rows 14:78) are populated, then the entire row (going out to column AE) will need to be copied.

I've attempted a range of codes but they're not quite right. For example, I've tried the following based on my attempt to record a macro, but when I add a new row of data to the range and run the code again, it doesn't select the new row.

Sub Activity_copy()
Range("C14").Select​

Selection.End(xlDown).Select​

Range(Selection, Selection.End(xlUp)).Select​

Range("C14:AE28").Select​

Range("C28").Activate​

Selection.Copy​

End Sub​

I've regularly adapted codes that I've found online and they've been fine but I cannot seem to find this scenario and a subsequent resolution.

Any assistance or code solutions would be greatly appreciated.

Thanks!

 

[Jamnbam]

Hello and welcome.


I've just tested this and it seems to fit what you ask, you just need to change WHERE the code is pasted to as you don't mention that.

Sub CopyRange()

    Dim rToCopy As Range
    Dim i As Integer
   
    For i = 14 To 78
        If Not IsEmpty(Range("C" & i)) Then
            If rToCopy Is Nothing Then
                Set rToCopy = Range("C" & i & ":AE" & i)
            Else
                Set rToCopy = Union(rToCopy, Range("C" & i & ":AE" & i))
            End If
        End If
    Next i
   
    rToCopy.Copy
    Range("CHANGEME").PasteSpecial xlPasteAll 'Change this line to include paste range 
 
End Sub

This code worked perfectly for my application as well. Thank you for posting this solution!