I have an data dump into an excel spreadsheet that has extra rows.  I
have a predefined format that I have to manually copy the data into. 
The predefined format has three worksheets.  I tried using macros but
they do not seem suited to a lot of copying and pasting.

---

Request for Question Clarification by shiva777-ga on 29 May 2003 18:08 PDT

This question would be very difficult to answer with a better idea of
the format of the data you're working with. Can you post up a sample?
thanks,
-shiva777

---

Clarification of Question by rooknyc-ga on 29 May 2003 19:50 PDT

The information is proprietary but is looks like this:

12000     1434321    0     0
(EMPTY ROW)
122712    1212122    121   122

1221200    0         12331  212343

(and on like this with some extra empty rows bunched together here and
there for 120 pages)

Hello there rooknyc-ga,

This problem is a fairly straight forward fix once you have got the
data into an Excel worksheet (which I'm assuming you've already done).

The following steps will "group" all of the empty rows:

1. Select all of the rows and columns that have been imported either
by dragging across with your mouse or using CTRL+A to select the
entire worksheet.

2. Go to Data > Sort from the top drop down menu.

3. Once the Sort dialog box appears, select one of your fields in the
"Sort By" section (preferably one where all valid data records have a
value there). It doesn't matter if you set the adjacent radio buttons
to ascending or descending as empty rows are always placed at the
bottom of the data set.

4. You don't need to sort by more than one row, but as you can see the
dialog box allows you to do this if you desire.

5. At the bottom of the dialog, select "header row" if your first row
in the worksheet has column names, or "no header row" if it doesn't.

The resulting data set should contain all valid records at the top,
sorted by the field you chose. Empty rows, if not followed by a row
containing data, are essentially non-existent so you don't have to
worry about removing these rows once the sort is complete.

Search Strategy:
personal experience

Hopefully this has answered your query, but if you are having problems
applying the information above please do feel free to post a
clarification and I will respond promptly :)


Cheers!

answerguru-ga

---

Request for Answer Clarification by rooknyc-ga on 30 May 2003 08:05 PDT

The data from the data dump is seperated into different groups.  If I
sort then the groupings are lost.

---

Clarification of Answer by answerguru-ga on 30 May 2003 08:56 PDT

Hi again rooknyc-ga,

If you need to maintain these groups then that begs the question "what
defines data as being in the same group?". If there is already a field
that exists which defines groups, then you just need to sort by that
column and all of your groups will be retained. If no such field
exists, create one (I would suggest a numeric flag field). The you
will need to go through and assign a number to each group, copy it so
that all members of the group have this same value. Then sort by this
flag field and you will have successfully eliminated the unnecessary
rows while keeping your groups intact.

answerguru-ga

I am not sure if you are satisfied with using the data sort function
that is described previously.  I have written a macro that I keep in
my personal macro book that will delete all empty rows.

Here is how it works.  You will select a starting point (cell), and
the macro will test every cell in this column and if the cell is empty
then the row is deleted.

Copy and paste the following into a module in the VBA editor and try
it out.  I works for what I want.  I hope that it works for you also.

The data between the horizontal lines should be copied and pasted.
_________________________________________________________________
Sub Delete_Rows()
'This sub will delete empty rows in the column selected.  Make sure
that the column selected does not have empty cells with data in other
column of the row.

Dim Last_row

Last_row = ActiveSheet.UsedRange.Rows.Count

mycell = InputBox("Enter the Cell Reference", "Enter Cell Starting
Point", "B2")
If mycell = "" Then GoTo lastline
Application.ScreenUpdating = False

Range(mycell).Select
mycell1 = Mid(mycell, 2)
Last_row = Last_row + mycell1

Do Until Selection.Row > Last_row

    If Selection.Value = "" Then
            Selection.EntireRow.Delete
            Last_row = Last_row - 1
            'Selection.Offset(2, 0).Select
        Else
            Selection.Offset(1, 0).Select
           
        End If
Loop
   
    Range(mycell).Select
    Application.ScreenUpdating = True
lastline:

End Sub
_______________________________________________________

This sub functions properly in Excel2002 and 2000.  

Good luck,
Deitzgen