CODEWITHSUNDEEP
vbaexcel
Rob H.
Rob H.

Reputation: 13

How to only copy cells that have data from one sheet to another and not leave blanks in between

I have a workbook with 4 worksheets and Sheet4 (titled "Deficiency List") is populated in 'column B' with values from other sheets based on different criteria. The list begins with "B1" and goes to "B1155". I would like to only copy the cells that have data in them and not the blanks to Sheet3 (titled "Deficiencies") starting at cell "B12".

Example, if there is only data on Sheet4 in cells: B3, B36, B756 and B1005, those would be copied to Sheet3 into cells: B12, B13, B14 and B15.

Here is the code I have to copy the data but it doesn't eliminate the blanks.

Sub Copy_Values()

Dim i As Long
Sheets("Deficiency List").Select
iMaxRow = 1155

    For iRow = 1 To iMaxRow

    With Worksheets("Deficiency List").Cells(iRow, 2)
        ' Check that cell is not empty.
        If .Value = "" Then
            'Nothing in this cell.
            'Do nothing.
        Else
            ' Copy the cell to the destination
            Worksheets("Deficiencies").Cells(iRow + 11, 2).Value = .Value
        End If
    End With

    Next iRow

End Sub

Upvotes: 0

Views: 68

Answers (2)

Tim Williams
Tim Williams

Reputation: 166351

Sub Copy_Values()

    Dim i As Long, d As Long, shtD, shtDL, tmp
    Dim iMaxRow As Long, iRow As Long

    Set shtD = Worksheets("Deficiencies")
    Set shtDL = Worksheets("Deficiency List")

    iMaxRow = 1155
    d = 12

    For iRow = 1 To iMaxRow
        With shtDL.Cells(iRow, 2)
            tmp = Trim(.Value)
            If Len(tmp) > 0 Then
                shtD.Cells(d, 2).Value = tmp
                d = d + 1
            End If
        End With
    Next iRow

End Sub

Upvotes: 0

MikeC
MikeC

Reputation: 958

    If (Trim$(CStr(.Value)) = "") Then

If you don't want rows in output area to be skipped,

  • declare a Long variable to hold destination row number
  • initialize it outside the loop to 11
  • in the else part, increment the variable and then use it as row number instead of irow + 11

Upvotes: 1

Related Questions