Search code examples
excelvbacopyrow

I want to copy a range to clipboard with criteria

I want copy a range with the following criteria

1 - Copy the entire row, including hidden columns

2 - If the Colunm "CC" has no value, them it will skip the entire row

Note Update = Sorry I forgot something, it won't always be two rows that will have data, sometimes they will have data in 3 4 5.... or in all the rows of the table

Note = My table has formulas so I don't want to mess with them, just copy the cell values

Im using this

Sub CopyVisible()
  Worksheets(1).Range("B3:I13").Select
    Selection.Copy
    
End Sub

And im getting this result

enter image description here

But i want this result

enter image description here

Im gonna copy to clipboard, then i will paste on another worksheet mannualy

With Xavier Junqué's code I'm having this result, the only problem is that he is selecting one more line

Code:

Sub CopyFilledCells()
    Dim rng As Range
    Set rng = Range("B3:I3")
    Worksheets(1).Activate
    For i = 3 To Worksheets(1).UsedRange.Rows.Count
        If Worksheets(1).Range("B" & i).Value = "" Then Exit For
    Next
    Range(rng, rng.Offset(i - 3, 0)).Select
    Selection.Copy
End Sub

Result:

enter image description here

Solution

  • Change instruction

    Worksheets(1).Range("B3:I13").Select

    to

    Worksheets(1).Range("B3:I4").Select

    Then, only two rows (rows# 3 and 4) between columns B and I will be copied. When pasting excel shows pasting options: select option copy only values.

    Perhaps try the following:

    Sub CopyFilledCells()
    Worksheets(1).Activate
    Worksheets(1).Range(Range("B3:I3"), Range("B3").End(xlDown)).Select
    Selection.Copy
End Sub

    Another try:

    Sub CopyFilledCells()
    Dim rng As Range
    Set rng = Range("B3:I3")
    Worksheets(1).Activate
    For i = 3 To Worksheets(1).UsedRange.Rows.Count
        If Worksheets(1).Range("B" & i).Value = "" Then Exit For
    Next
    Range(rng, rng.Offset(i - 3, 0)).Select
    Selection.Copy
End Sub