Results 1 to 10 of 10

Thread: Delete rows

  1. #1
    New Lounger
    Join Date
    Jun 2003
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Delete rows

    There is an auto report that I can access that has data I use. I need a macro to remove the formating it has first. Starting in A1 a I need to delete any row that doesn't contain an integer in that column. The only other things would be a blank cell or text. The loop could end after it had encounter 5 consecutive blank cells or looped 500 times (the max number of rows involved) If someone could help this macro novice I would appreciate it <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

  2. #2
    ScoobyDoo!
    Guest

    Re: Delete rows

    This may not be the most elegant solution, but it will certainly work for you:

    <pre>Sub DeleteEmptyRows()

    Range("a1:a500").Select
    Selection.Replace What:="", Replacement:="X", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False

    For x = 1 To 500
    If IsNumeric(Range("a" & x).Value) = False Then
    Range("a" & x).Activate
    ActiveCell.EntireRow.Delete
    x = x - 1
    End If

    Next x

    End Sub
    </pre>


    Hope this helps!

  3. #3
    New Lounger
    Join Date
    Jun 2003
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete rows

    Thank You Scooby,it looked elegant to me, it did just what I needed it to do.

  4. #4
    ScoobyDoo!
    Guest

    Re: Delete rows

    Glad to help.
    Have a great weekend!

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete rows

    As long as there is something in at least one cell of row 1, the following macro should do what you want:

    <pre>Public Sub DeleteUnwantedRows()
    Dim iRows As Integer, I As Integer
    iRows = Sheet1.UsedRange.Rows.Count
    For I = iRows To 1 Step -1
    If (Not IsNumeric(Sheet1.Cells(I, 1))) Or (Trim(Sheet1.Cells(I, 1)) = "") Then
    Sheet1.Cells(I, 1).EntireRow.Delete
    End If
    Next I
    End Sub
    </pre>

    Legare Coleman

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,469
    Thanks
    30
    Thanked 61 Times in 57 Posts

    Re: Delete rows

    Here's an interesting case in point (BTW, I'd e-mail you privately about this nonsense but don't have an addr for you).

    Your VBA solution inserted a character in the user's text (sure, temporary, but something that wasn't there before).

    FYI: APL has a generalize inner product (matrix product) that enables you to generalize any function and apply the logic of inner product (which is summing after multiplication, essentially). So, one part of this solution is to find out where in the data you have at least one non-blank. APL has a "logical or" symbol (which almost looks like a "v"), and a "not equal" symbol (I'll use the | to mean that) so the notation would be:

    data v.|' '

    the above would give you a boolean string of 1s and 0s "flagging" there at least one element of each row in data was not equal to a blank. That's a start...the rest is just as simple.

    (data {or}.{not equal}' ') {and} (data {and}.{element of}'0123456789 ')

    too hard to illustrate with the lack of meaningful symbols available here.

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete rows

    Why would you have wanted to send this as a private email?

    I think that, if you will go back and read my message, you will find that my code did not insert anything into the user's text. It simply did what the user wanted and deleted the unwanted rows.

    What is the connection of inner products and APL got to do with deleting rows in an Excel spreadsheet? If you want to discuss APL, I think you are in the wrong place.
    Legare Coleman

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,469
    Thanks
    30
    Thanked 61 Times in 57 Posts

    Re: Delete rows

    I really meant to start this message as "to" ScoobyDo...we were having an Excel and array conversation as part of another thread and this was an example for me to show him.

  9. #9
    Star Lounger
    Join Date
    Jan 2001
    Location
    Newcastle, New South Wales, Australia
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete rows

    Maybe this is too late for you, but you don't need to use a macro. If the order of the rows is important, then insert a column and autofill it from 1 to whatever you bottom line is. If the order doesn't matter you can skip this step, but I always do it anyway just to be able to retrieve my original.
    Then select the whole data set and sort on the column that was A. It will sort numbers, then text then blanks. Go to the start of the text, press shift-ctrl-end to select to the end of the data and delete the rows (Edit Delete EntireRow). Then re-sort on the inserted column to return to the original order.

    One advantage of this is that you can see that the data are as you expected they should be.
    If you have to distinguish between integers and other numbers, then this won't work as is, but if it's really just between numbers, text and blanks, then it will

    Ruth

  10. #10
    New Lounger
    Join Date
    Jun 2003
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete rows

    Thanks to everyone who offered their help. It worked just the way I needed. It gets the data ready to be imported into an Access database. With the help of another macro (I recorded)I'm able to further seperate the data for tracking time. Thanks again for the help.

Posting Permissions

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