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

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

Ask Your Question

Weekly Poll

Would you be willing to go on a SpaceX trip around the moon?

Discuss in The Lounge

Poll History