Inefficient macro (Excel xp)

Topic

New Reply

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

Reply | Quote

Replies

Did you intend to include or attach your macro code?

Reply | Quote

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

Reply | Quote

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:

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

Reply | Quote

I wouldn’t dare to try to improve on the solution offered by Legare , 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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

(Edited by AlanMiller on 22-Apr-03 02:47. )

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 .

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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).

Reply | Quote

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.

Reply | Quote

That’s nice, but it will also delete empty rows within the used range. People often use those to make a worksheet more readable. I think the recent posts in this thread were concerned with getting rid of unused rows (and columns) below (and to the right of) the “last used cell”.

Reply | Quote

Neat trick Errol.
I’ll keep a note of that as a generic method of “capturing” then restoring the row ordering of a SS.

thanks

Alan

Reply | Quote

Alan,
I described the “index column” sorting method in a generic way just to pass on the idea. You can do any kind of sort to group the rows, then have your way with the data. The sheet I work on does have blank rows when I’m done because of the way I sort and the design of the rows. Also, the rows have many links to other sheets, and all that sorting and deleting has no affect on the links.

Errol

Reply | Quote

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

Reply | Quote

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….

Reply | Quote

As a side note, Jan Karel pieterse in post 180759 demonstrated a very slick Autofilter way to delete blanks in a particular column. It will not fit specific or complex requirements such as “if the row A cell is blank and the row D cell contains ‘**’ and today is Tuesday”, but it can use any criteria available in AutoFilter, including filtering on blanks and spaces. The generic version of Jan Karel’s code which I use regularly on a certain report I receive is:

Sub DelBlanks()
ActiveSheet.UsedRange.AutoFilter Field:=1, Criteria1:=”=” ‘ filter for blanks, including spaces
‘ next line assumes that first UsedRange.Row is Filter data or Filter Headers
Rows(ActiveSheet.UsedRange.Row + 1 & “:” & ActiveSheet.UsedRange.Row _
+ ActiveSheet.UsedRange.Rows.Count).Delete Shift:=xlUp
ActiveSheet.AutoFilterMode = False
End Sub

If you can use it, it sure beats coding a loop.

Reply | Quote

A little trick that also speeds up macros is

At the beginning of your macro..
Application.Screenupdating = False

Then at the end of your macro…
Application.Screenupdating = True

Of course, if you have any interaction with the user, this won’t work. You’d need to hop in and out of the screen updating.

Reply | Quote