Copy and paste rows between sheets 1

[Kayrow]

I'd like to cycle through about 1000 rows in a sheet and copy all rows that have data into another sheet. OR simply delete all the completely blank rows. Has any one got some code to do this?

 

[lambuhere1]

For i = 1000 to 1 step -1


if cells(i,1).value = "" then

cells(i,1).select
selection.entirerow.delete
end if

Next i

Tweak this code to meet your requirements.

Ram P

 

[acron]

Or just do a sort that palces the blanks at the end of your data.

A.C.

 

[Kayrow]

Thanks lambuhere1. Works like a dream. I've chucked some code in to find the last row rather than going to a thousand, so it looks like:

Global IntRowStart As Integer
Sub Delete()
range("A1&quot.Select
selection.End(xlDown).Select
ActiveCell.Offset(1, 0).range("A1&quot.Select
IntRowStart = selection.Row
For i = IntRowStart To 1 Step -1
If Cells(i, 1).Value = "" Then
Cells(i, 1).Select
selection.EntireRow.Delete
End If
Next i
End Sub