Collectiong, sorting, but skipping blank rows

May 19, 2017 at 01:57:52
Specs: Windows 7

I need some help on the similar task (vertical) please.
There is 1200 cells in one column of mine (on one sheet) and data is written in few rows and then few blank, few filled, few blanked...etc
Now, I would like to have it on the other sheet, but without blank rows.

Thank you!

message edited by mihaela000

See More: Collectiong, sorting, but skipping blank rows

Reply ↓  Report •

May 22, 2017 at 06:50:03
Without knowing the EXACT layout of your workbook I can only offer a generic solution which you can modify for your needs.

Private Sub CopyData()
    ' Define the variables
    ' Bcell is a reference to each cell within a range
    ' NextRow is the next available row on the sheets being copied to
    Dim Bcell As Range
    Dim NextRow As Long
    ' Loop through each cell in your main sheets
    For Each Bcell In Range("A1", Range("A" & Rows.Count).End(xlUp))
    ' Set NextRow as the next available (empty) cell in sheet 2
        NextRow = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row

    ' On your main sheet if a cell in column A is NOT empty then copy the entire row
        If Bcell.Value <> Empty Then


    ' Paste the copied row to the next sheet
            If Sheets("Sheet2").Range("A1") = Empty Then
                Sheets("Sheet2").Range("A" & NextRow + 1).PasteSpecial
            End If

         End If
    Next Bcell
End Sub

Reply ↓  Report •
Related Solutions

Ask Question