Solved Collectiong, sorting, but skipping blank rows

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

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

Report •

#1
May 22, 2017 at 06:50:03
✔ Best Answer
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

            Bcell.EntireRow.Copy

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

         End If
     
    Next Bcell
    
End Sub


Report •
Related Solutions


Ask Question