Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Jul 2003
    Location
    london
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro to delete rows (2000)

    Pls pls pls could someone give me some guidance on how to delete every other row in excel, I'm going to need to do this every week for spreadsheets differing in size - how do I get excel to check the size of the sheet before it begins?

  2. #2
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to delete rows (2000)

    Katharine,

    By checking the size of the sheet, I assume you mean how many rows the sheet contains. Do the rows to be deleted contain data?

  3. #3
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Macro to delete rows (2000)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Katharine

    OK a couple of quick questions for you...

    1) You want to delete every other row, does that mean all Even-numbered rows, since the worksheet starts with row 1, I assume you want to delete rows 2,4,6,8,10, and so on...?

    2) When you say, different size, you mean some week you get 10 rows worth of data, and the other week you may get 10,000 rows worth of data. If this is what I understand you have multiple ways, each has its advantages and disadgantages.

    You can use the UserRange. The advantage of this is its Excel's buitl-in way of finding the range you need. The disadvantage is that it uses the "Last Occupied Cell" as its marker, and the "Last Occupied Cell" can change and Excel will have a rough time getting at it.

    You can Use .End(xlDown) to count the rows that contain data. Sometimes I think this is the best way, but again the data can be such that some columns may have empty cells and that will defeat the .End directional count of rows.

    You can use the VBA built-in range finder Current Region, and I don't know if this also have the problem of the "Last Occupied Cell".

    So maybe you can use a combination of these techniques and then see which one will work for you. I hope that someone from this Lounge will chime in and tell us the finer points of using these techniques.

    Now a word of <font color=red>caustion</font color=red> When deleteing rows, it is best done bottom-to-top, so your For Next Loop should be something like:

    For lRowTodelete = lNumberOfRowsInRangeOfData to 1 Step -1 or <font color=blue>maybe -2</font color=blue>
    your code to detele the rows will be here.
    Next lRowTodelete

    I also will point you to Microsoft Knowledge Base Article - 213631
    and Microsoft Knowledge Base Article - 213610
    maybe you can see this example and tailor it to your needs.

    If you need more help, simply reply with more info or questions.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  4. #4
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Macro to delete rows (2000)

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> Steve

    But your missed explaining what <font color=blue>iOffset</font color=blue> will do?

    Please could you re-edit your reply to Kathreine so that she will know hoe to work it.

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>You also forgot to declare it.

    Thanks again.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  5. #5
    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: Macro to delete rows (2000)

    Try this. It checks col 1 (A) to see the last "used row" if you want to "check" with a different col change in the code (iCol). It assumes every other row (change with iStep), starting with row 2 (change lstartRow). I have the code written to only change the background color of the lines that it would delete with yellow (to test it). Add a comment to this line (or delete the line) and remove the comment from the delete line once it works as you expect.

    Steve

    <pre>Sub DeleteEveryOther()
    Dim rng As Range
    Dim lRow As Long
    Dim lRows As Long
    Dim iCol As Integer
    Dim lStartRow As Long
    Dim iStep As Integer
    iCol = 1 'Change as appropriate
    lStartRow = 2 'Change as appropriate
    iStep = 2 'Change as appropriate
    Set rng = Cells(lStartRow, iCol)
    lRows = Range("A65536").End(xlUp).Row

    For lRow = lStartRow + iStep To lRows Step iStep
    Set rng = Union(rng, Cells(lRow, iCol))
    Next
    rng.EntireRow.Interior.Color = vbYellow 'Use this to test
    'rng.EntireRow.Delete 'remove the comment after testing
    Set rng = Nothing
    End Sub</pre>


  6. #6
    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: Macro to delete rows (2000)

    Wassim,
    Thanks for catching my oversight. There should be no iOffset variable used.

    I originally had called and tested it with iOffset but when I posted it and was explaining it, I thought the name iStep was more appropriate and I changed it. I missed one of the occurrences of the variable (that is what I get for not retesting it in VB). I change the code in the original post to reflect this.

    Steve

Posting Permissions

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