Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Inefficient macro (Excel xp)

    I think the following macro that i've written takes too long and is inefficient but i don't know how to correct it. Could someone help me with it. I want to delete all rows that are empty OR have a spaceband " " in them. I use this macro to delete rows from csv's that i get and they "look" blank but have a space in one of the cells. These rows still need to be deleted. Thank you

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Inefficient macro (Excel xp)

    Did you intend to include or attach your macro code?

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Inefficient macro (Excel xp)

    oops sorry.

    Sub DeleteRowsSimple()
    Dim I As Long

    On Error Resume Next
    With ActiveSheet
    lLastRow = .Range("A65536").End(xlUp).Row - 1
    For I = lLastRow To 0 Step -1
    If .Range("B" & I).Value = "" And .Range("C" & I).Value = "" And .Range("D" & I).Value = "" And .Range("E" & I).Value = "" And .Range("F" & I).Value = "" And .Range("G" & I).Value = "" And .Range("H" & I).Value = "" And _
    .Range("A" & I).Value = "" Or .Range("A" & I).Value = " " Then
    .Range("A" & I).Offset(0, 0).EntireRow.Delete
    End If
    Next I
    End With
    Range("A1").Select
    End Sub

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

    Re: Inefficient macro (Excel xp)

    Your macro does not seem to do exactly what you described in your first post. I think that the code below does what you described:

    <pre>Sub DeleteRowsSimple()
    Dim I As Long, J As Long
    Dim lLastRow As Long
    Dim strWk As String
    With ActiveSheet
    lLastRow = .UsedRange.Row + .UsedRange.Rows.Count - 2
    For I = lLastRow To 0 Step -1
    strWk = ""
    For J = 0 To 7
    strWk = strWk & .Range("A1").Offset(I, J).Value
    Next J
    If Trim(strWk) = "" Then
    .Range("A1").Offset(I, 0).EntireRow.Delete
    End If
    Next I
    End With
    Range("A1").Select
    End Sub
    </pre>

    Legare Coleman

  5. #5
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inefficient macro (Excel xp)

    I wouldn't dare to try to improve on the solution offered by Legare <img src=/S/grin.gif border=0 alt=grin width=15 height=15>, but I'd just add that I've been caught with such "inefficient" code myself. The key to improvement lies in identifying the used range and processing only within that, rather than the entire (very large, and largely empty) sheet. This is exactly what Legare has done in his solution.

    Alan

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

    Re: Inefficient macro (Excel xp)

    Good point, but not exactly in this case. What you say is frequently true. However, in this case, I used the UsedRange property to identify the last row used, even if it did not have anything in column A in that row. I then process from there up to the top row because it looked like jha900 wanted to delete unused rows at the top of the sheet also. The way he was finding the last row would not work if there were rows at the bottom of the sheet with empty cells in column A.
    Legare Coleman

  7. #7
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Inefficient macro (Excel xp)

    thank you for the advice. the part i have difficulty with is knowing how to code the range in which to loop. i see this concept throughout the code he gives me and i'm hoping that eventually a light will go on and i will see all.... <img src=/S/eyeout.gif border=0 alt=eyeout width=15 height=15>

  8. #8
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inefficient macro (Excel xp)

    Thanks for the qualification Legare. I must admit to not looking deeply enough to spot that issue. The one that always dragged down the performance of some of my early macro attempts, was iterating the whole sheet rather than just the used range. But this pops up a new question:

    Does the used range always begin at A1?

    If I created a new sheet and started entering data into D5, and maintained cell D5 as the top left cell for data entry, what would the used range look like?

    Alan

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

    Re: Inefficient macro (Excel xp)

    No, the UsedRange does not always start at A1. The UsedRange starts at the cell at the intersection of the first row and first column that contain data. So, if the first row that contains data is row 5, and the first column that contains data is D, and the last row that contains data is row 50, and the last column that contains data is column G, then the UsedRange is D5:G50. That is why I added UsedRange.Row (five in the previous example) and UsedRange.Rows.Count (46 in the previous example) to find the last row.
    Legare Coleman

  10. #10
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inefficient macro (Excel xp)

    <P ID="edit" class=small>(Edited by AlanMiller on 22-Apr-03 02:47. )</P>Excellent! Thanks for clearing that one up. I was pretty sure where the used range finished; always a bit unclear on where it started though. Now I can sleep easy <img src=/S/snore.gif border=0 alt=snore width=32 height=15>.

    And just to qualify this further, would it also be correct to describe it as the "minimal" rectangle that contains all of the non-empty cells?

    Alan

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

    Re: Inefficient macro (Excel xp)

    Yes, as long as the workbook has been saved since the last time cells were cleared or deleted. When cells are cleared or deleted, Excel does not adjust the UsedRange to reflect that until the workbook is saved.
    Legare Coleman

  12. #12
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inefficient macro (Excel xp)

    Thank you again Legare. You've also preempted my question about "deleted" cells being included in the UsedRange, which is something I've observed. However, I seem to recall an issue with the UsedRange sometimes spreading into empty regions, even after the sheet is saved and reopened. I've certainly noticed this when people have "fiddled" with a worksheet, but have not permanently added any extra cells by the time they'd resaved it and I get it back. Is this not one of the "bloat" issues that can be addressed, by copying to a new sheet and resaving the workbook? Maybe old "ghost" formatting remains in those "empty" cells?

    Alan

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Inefficient macro (Excel xp)

    Just copying the entire contents of a sheet to a new sheet won't help.
    Saving a workbook as HTML and then converting to Excel again gets rid of unused cells (in Excel 2000 and up).

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

    Re: Inefficient macro (Excel xp)

    FWIW:
    I have a spreadsheet where I need to delete many unused rows, too. Once I establish the total range of rows, I insert a new column A, fill it with consecutive numbers from the first to the last used rows. This is for indexing. Then I SORT on a column just to get the blank "" and space " " rows together. FIND them, DELETE them, and, finally SORT on the Column A index. Delete the temporary Column A, and the empty rows will be gone.

    This method uses more of Excel's methods to do the looking.

  15. #15
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inefficient macro (Excel xp)

    Sorry, I meant copying just those cells containing visible data to a new sheet. But your method is, no doubt, safer since no data can be accidentally omitted from the copy.

    thanks

    Alan

Page 1 of 2 12 LastLast

Posting Permissions

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