Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Feb 2002
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Deleting parts of a spreadsheet (2000)

    I would welcome any advice on how to simplify the following situation.

    I often get spreadsheet with a few thousand rows of data. Every second row of the information needs to be deleted. The number of columns in each spreadsheet may vary. Apart from selecting every second row and deleting, is there an easy way that I can do this?

    Thanks in advance.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Deleting parts of a spreadsheet (2000)

    2 solutions come to mind:
    1) a macro to go through the list looking for blanks and deleting the appropriate rows
    2) a non macro solution. Put on autofilter (data-filter-autofilter). select a column and choose "Blank" from the list. This will filter out everything buth the blanks. Then highlight all of these and delete the rows

    If you find that you have some blanks in that column but NOT in other columns and you want to make sure that the Entire range is "blank":
    Add a temporary column
    add the formula (change as appropriate):
    <pre>=Counta(A1:S1) </pre>

    copy this down the rows
    Add the Autofilter and autofilter on THIS column for zero (0). this will be ALL BLANKS in the row. Highlight the rows and delete.

    The advantage of the non-macro is (if you are anal) you can SEE the rows you will be deleting BEFORE they are deleted to ensure that they are blank

    Steve

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting parts of a spreadsheet (2000)

    I think this works for a macro solution:

    Sub DelRows()
    Dim a As Integer
    For a = ActiveSheet.UsedRange.Rows.Count To 2 Step -2
    ActiveSheet.Cells(a, 1).EntireRow.Delete
    Next
    End Sub

    Caution though, as Steve points out, this will delete every second row whether it has data or not and also every second row out to the end of where you have information...whether it is part of the data you wish to change or some other part of the spreadsheet.

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Deleting parts of a spreadsheet (2000)

    This will delete all blank rows in the user selected area without regard to recurrence:

    Sub DeleteBlankRows()
    Dim intC As Integer
    Application.ScreenUpdating = False
    For intC = Selection.Rows.Count + Selection.Row To Selection.Row Step -1
    If Application.WorksheetFunction.CountBlank(Rows(intC ).EntireRow) = _
    Application.Columns.Count Then Rows(intC).Delete Shift:=xlUp
    Next intC
    Application.ScreenUpdating = True
    End Sub
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting parts of a spreadsheet (2000)

    A non-macro solution. In the first cell in the first blank column put the formula:

    =MOD(ROW(),2)

    Double click the fill handle (assuming no blank rows, otherwise drag the fill handle to the bottom of your data). Your now have alternating 1's and 0's beside your data; they are ready selected. Copy the selection, then do a Paste Special / Values on top of itself.

    Now sort on the new column, then select either the 1's or the 0's and delete the whole rows.

  6. #6
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    Paragould, Arkansas, USA
    Posts
    194
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting parts of a spreadsheet (2000)

    Mitch doesn't say anything about any rows being blank. However, the advice here is still good. If every other row (the ones to be deleted) have something common, but different from the "good" rows (example: Column B on the "bad" rows might have the word "Subtotal"), then use the filter to sort for that.

    Errol

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •