Copy only cells with data within a range using VBA

[Drewmyster]

Here is the code I use;

Private Sub CommandButton1_Click()
Range("G4:G200").Copy
End Sub

If cells are blank how do I remove them from being copied?

 

[Rick Rothstein]

What is in cells G4:G200...
Formulas or constants?
What type of values...text or numbers?

 

[Drewmyster]

What is in cells G4:G200...
Formulas or constants?
What type of values...text or numbers?

Formulas collating data from other cells (CONCATENATE) the data result is combination of text and numbers.

 

[kevin9999]

Please try the following on a copy of your workbook:

Private Sub CommandButton1_Click()
    Dim c As Range, r As Range
    For Each c In Range("G4:G200").SpecialCells(xlCellTypeFormulas)
        If c <> "" Then
            If r Is Nothing Then Set r = c Else Set r = Union(r, c)
        End If
    Next c
    
    r.Copy

End Sub

 

[Drewmyster]

Please try the following on a copy of your workbook:

Private Sub CommandButton1_Click()
    Dim c As Range, r As Range
    For Each c In Range("G4:G200").SpecialCells(xlCellTypeFormulas)
        If c <> "" Then
            If r Is Nothing Then Set r = c Else Set r = Union(r, c)
        End If
    Next c
   
    r.Copy

End Sub

Thanks that has worked, I just noted that one cell to copy will always be text and not a formula, is there a way to include this in the same macro?

 

[kevin9999]

Sure, just lose the special cells part:

Private Sub CommandButton1_Click()
    Dim c As Range, r As Range
    For Each c In Range("G4:G200")
        If c <> "" Then
            If r Is Nothing Then Set r = c Else Set r = Union(r, c)
        End If
    Next c
    
    r.Copy

End Sub