How to copy a range and omit blank cells?

Question

In column A on sheet 1 there are 3000 cells that I need copied at 350 cells each. My current Macro is copying everything just fine until I get to the end and it copies blanks. Is there a way to include a "cell is blank do nothing" code into my macros?

Sorry if this sounds uneducated, I'm just starting on learning macro. Here is a copy of the current macro, the rest of the macro is the same as this just with increasing numbers by 350.

Sub Copy_Bins_1_350()
    If Range("D12").Value <> "!" Then
        Exit Sub
    ElseIf Range("D12").Value = "!" Then
        Sheets("sheet1").Select
        Range("B2:B351").Select
        Selection.Copy
        Range("B2").Select
        Sheets("sheet2").Select
        Range("E12").Select
        With Selection.Font
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
        End With
    End If
End Sub

score 0 · Accepted Answer · edited May 23 '17 at 12:08

0

You can use Union to form your own range of non empty cells and then copy them.

Also INTERESTING READ

Try this (TRIED AND TESTED)

Sub Sample()
    Dim wsI As Worksheet, wsO As Worksheet
    Dim aCell As Range, rngCopyFrom As Range, rng As Range
    Dim lRow As Long

    Set wsI = ThisWorkbook.Sheets("BIN LIST PASTE")
    Set wsO = ThisWorkbook.Sheets("BIN LIST COPY")

    Set rng = wsI.Range("B2:B351")

    For Each aCell In rng
        If Len(Trim(aCell.Value)) <> 0 Then
            If rngCopyFrom Is Nothing Then
                Set rngCopyFrom = aCell
            Else
                Set rngCopyFrom = Union(rngCopyFrom, aCell)
            End If
        End If
    Next

    If Not rngCopyFrom Is Nothing Then _
    rngCopyFrom.Copy wsO.Range("E12")

    With wsO
        lRow = .Range("E" & .Rows.Count).End(xlUp).Row

        Set rng = .Range("E12:E" & lRow)

        With rng.Font
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
        End With
    End With
End Sub

edited May 23 '17 at 12:08

Community

1
1

answered Dec 16 '13 at 03:21

Siddharth Rout

147,039
17
206
250

Thanks for the reply, however, this copies my information into another page where as I only need it to just copy for me everything but the empties. The stuff I am copying will be pasted into a whole other program and this program messes up if there are blanks. I tried messing with it too and reading your link, its very very interesting, thank you for providing it to me. I am thinking of using something along the lines of selecting a range and putting an If:Then command in it. EG. If blank then Do not copy. Or something of that caliber. Im just not sure how to make that into the excel language – Forbidden Dec 16 '13 at 05:28
Yes it will copy it to `BIN LIST COPY` sheet. Didn't you want to copy it there? – Siddharth Rout Dec 16 '13 at 05:29
Actually no, even though the page is titled in such a manner I really just need the bins copied to paste them in another program later on. – Forbidden Dec 16 '13 at 05:45
In that case change the line `If Not rngCopyFrom Is Nothing Then _ rngCopyFrom.Copy wsO.Range("E12")` to `If Not rngCopyFrom Is Nothing Then rngCopyFrom.Copy` and then the data is stored in the clipboard. Paste it wherever you want :) – Siddharth Rout Dec 16 '13 at 05:46

How to copy a range and omit blank cells?

1 Answers1

Linked