Now that I know how to scroll thru all of the rows looking for which
one has data.
How can I select all the rows and copy them to a new worksheet if there
is data in Column A.

I need to remove all of the blank rows from my spreadsheet. For some
reason even if the cell has no data excel saves it. So when I hit
Ctrl-End it doesn't go to the last cell with data but about 3000 rows
further.

Any Ideas

Sub vexed()
Dim r As Range
Set r = ActiveSheet.UsedRange
il = r.Rows.Count + r.Row - 1
Sheets("Sheet1").Select
k = 1
For ii = 1 To il
If IsEmpty(Cells(ii, 1)) Then
Else
Cells(ii, 1).EntireRow.Copy Sheets("Sheet2").Cells(k, 1)
k = k + 1
End If
Next
End Sub

This will search down column A in Sheet1; if it finds a non-empty cell it
will copy the entire row to Sheet2.
--
Gary''s Student


"VexedFist" wrote:

Now that I know how to scroll thru all of the rows looking for which
one has data.
How can I select all the rows and copy them to a new worksheet if there
is data in Column A.

I need to remove all of the blank rows from my spreadsheet. For some
reason even if the cell has no data excel saves it. So when I hit
Ctrl-End it doesn't go to the last cell with data but about 3000 rows
further.

Any Ideas