delete rows macro (Excel 2000)

Topic

New Reply

I need to delete blank rows. Column A is used to number the rows in each section of the spreadsheet. Only the sum function is used in various columns. So this is the task: If Column B is blank and the result of the sum function is 0, then delete the row. I tried this macro and it didn’t accomplish what I needed. Thank you.

Sub deleteEmptyrows()
Dim rowNum As Long
Lastrow = ActiveSheet.UsedRange.row – 1 + _
ActiveSheet.UsedRange.rows.Count
Application.ScreenUpdating = False
For rowNum = Lastrow To 1 Step -1
If Application.CountA(rows(rowNum)) = 0 Then _
rows(rowNum).delete
Next rowNum
End Sub

Reply | Quote

Replies

How do we test for:”the result of the sum function is 0″?

I suspect changing your line to:
If isempty(cells(rowNum,2)) and _
vResult = 0 Then _
rows(rowNum).delete

You will have to give me a clue on how to define “vResult”

Steve

Reply | Quote

The test for CountA = 0 tests that there are NO non-blank cells in a row. From your description, I gather that column A is always populated, so the count would never be 0. Cells with formulas don’t count as blanks either. Can you try to describe more precisely what you want, and perhaps attach a small demo worksheet?

Reply | Quote

Here is a sample. I need to delete rows 22 – 30 and 39 – 69.

Reply | Quote

Why do you want to delete row 22 and 23, but not 31 and 32?
Why do you want to delete row 39, but not 35 – 37?

Reply | Quote

*hoping* That was a typo on her part…

Reply | Quote

*hoping* That was a typo on her part…

Reply | Quote

It was a typo. I was looking at Column A instead of the actual row number when I was writing the post.

Reply | Quote

The question remains, why do you want to delete row 39 but not 35 – 37.

Reply | Quote

Probably because those rows separate a different section. I guess that she wants to delete all rows which have a number in column A but no customer in the next column of that row. Something like:

Sub DeleteEmptyrows()
Dim rowNum As Long, Lastrow As Long
Lastrow = ActiveSheet.UsedRange.Row – 1 + _
ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For rowNum = Lastrow To 1 Step -1
If IsNumeric(Cells(rowNum, 1)) And _
Cells(rowNum, 2).Value = “” And _
Cells(rowNum, 13).Value = 0 Then _
Rows(rowNum).Delete
Next rowNum
End Sub

Reply | Quote

I was thinking she wanted to delete those without values in column M (companies with no purchases or whatever….in which case I just made a code that would work for me….) the following code would remove up to 500 instances of ‘0’. I’m still just learning, anyone who would like, please nitpick this code:

Function DeleteEmptyRows()
Dim i As Integer
Dim intRowCount As Integer
intRowCount = Range(“B1”).Select
For i = 1 To 500

Do
If Not (ActiveCell) = 0 Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until (ActiveCell) = 0

Dim R As Long
Dim C As Range
Dim Rng As Range

If (ActiveCell) = 0 Then
Set Rng = Selection
Else
Set Rng = ActiveSheet.UsedRange.Rows
End If
For R = Rng.Rows.Count To 1 Step -1
If (ActiveCell) = 0 Then
Rng.Rows®.EntireRow.Delete
End If
Next R

Next i

End Function

Reply | Quote

If the OP wants to delete all rows with a number in Col A and a zero in Col M, then she would omit

Cells(rowNum, 2).Value = “” And _

in the code I posted.

In Excel it’s always better to Dim a row counter as Long, since the maximum number of rows in an Excel spreadsheets since … XL95? … is greater than an Integer Type can hold, though you are safe if you want to quit at 500 deletes. Also ‘ intRowCount = Range(“B1”).Select’ means nothing, and it and some other variables do not appear to be used in your subsequent code. Finally, you don’t need to activate a cell to operate on it; Activating slows the code down. The offset method is good to learn for use to iterate through a range.

Editing your code -without testing-, this might be simpler:

Function DeleteEmptyRows()
Dim i As Integer
Dim R As Long
Dim Rng As Range

Set Rng = ActiveSheet.UsedRange.Rows
For R = Rng.Rows.Count To 1 Step -1
If Offset(Rng, R, 13).Value = 0 Then ‘ see if the cell 13 columns right of the leftmost column in the Usedrange is zero
Rng.Rows®.EntireRow.Delete
i = i + 1 ‘ increment the counter for 500
End If
If i = 500 Then Exit For ‘ stop processing
Next R

Set Rng = Nothing ‘ empty the range object before the code ends
End Function

There’s a whole lot more, for example, UsedRange has to be used carefully, as it may not return what you expect. In the above example, the code may be wrong if column A is not used.

Reply | Quote

If the OP wants to delete all rows with a number in Col A and a zero in Col M, then she would omit

Cells(rowNum, 2).Value = “” And _

in the code I posted.

In Excel it’s always better to Dim a row counter as Long, since the maximum number of rows in an Excel spreadsheets since … XL95? … is greater than an Integer Type can hold, though you are safe if you want to quit at 500 deletes. Also ‘ intRowCount = Range(“B1”).Select’ means nothing, and it and some other variables do not appear to be used in your subsequent code. Finally, you don’t need to activate a cell to operate on it; Activating slows the code down. The offset method is good to learn for use to iterate through a range.

Editing your code -without testing-, this might be simpler:

Function DeleteEmptyRows()
Dim i As Integer
Dim R As Long
Dim Rng As Range

Set Rng = ActiveSheet.UsedRange.Rows
For R = Rng.Rows.Count To 1 Step -1
If Offset(Rng, R, 13).Value = 0 Then ‘ see if the cell 13 columns right of the leftmost column in the Usedrange is zero
Rng.Rows®.EntireRow.Delete
i = i + 1 ‘ increment the counter for 500
End If
If i = 500 Then Exit For ‘ stop processing
Next R

Set Rng = Nothing ‘ empty the range object before the code ends
End Function

There’s a whole lot more, for example, UsedRange has to be used carefully, as it may not return what you expect. In the above example, the code may be wrong if column A is not used.

Reply | Quote

I was thinking she wanted to delete those without values in column M (companies with no purchases or whatever….in which case I just made a code that would work for me….) the following code would remove up to 500 instances of ‘0’. I’m still just learning, anyone who would like, please nitpick this code:

Function DeleteEmptyRows()
Dim i As Integer
Dim intRowCount As Integer
intRowCount = Range(“B1”).Select
For i = 1 To 500

Do
If Not (ActiveCell) = 0 Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until (ActiveCell) = 0

Dim R As Long
Dim C As Range
Dim Rng As Range

If (ActiveCell) = 0 Then
Set Rng = Selection
Else
Set Rng = ActiveSheet.UsedRange.Rows
End If
For R = Rng.Rows.Count To 1 Step -1
If (ActiveCell) = 0 Then
Rng.Rows®.EntireRow.Delete
End If
Next R

Next i

End Function

Reply | Quote

Probably because those rows separate a different section. I guess that she wants to delete all rows which have a number in column A but no customer in the next column of that row. Something like:

Sub DeleteEmptyrows()
Dim rowNum As Long, Lastrow As Long
Lastrow = ActiveSheet.UsedRange.Row – 1 + _
ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For rowNum = Lastrow To 1 Step -1
If IsNumeric(Cells(rowNum, 1)) And _
Cells(rowNum, 2).Value = “” And _
Cells(rowNum, 13).Value = 0 Then _
Rows(rowNum).Delete
Next rowNum
End Sub

Reply | Quote

The question remains, why do you want to delete row 39 but not 35 – 37.

Reply | Quote

Try this – give it a second to run through….

Function DeleteLoop()

Range(“M1”).Select
Do
If Not (ActiveCell) = 0 Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until (ActiveCell) = 0

Call DeleteEmptyRows
End Function

Sub DeleteEmptyRows()

Dim R As Long
Dim C As Range
Dim Rng As Range

If (ActiveCell) = 0 Then
Set Rng = Selection
Else
Set Rng = ActiveSheet.UsedRange.Rows
End If
For R = Rng.Rows.Count To 1 Step -1
If (ActiveCell) = 0 Then
Rng.Rows®.EntireRow.Delete
End If
Next R

Call DeleteLoop

End Sub

Reply | Quote

Try this – give it a second to run through….

Function DeleteLoop()

Range(“M1”).Select
Do
If Not (ActiveCell) = 0 Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until (ActiveCell) = 0

Call DeleteEmptyRows
End Function

Sub DeleteEmptyRows()

Dim R As Long
Dim C As Range
Dim Rng As Range

If (ActiveCell) = 0 Then
Set Rng = Selection
Else
Set Rng = ActiveSheet.UsedRange.Rows
End If
For R = Rng.Rows.Count To 1 Step -1
If (ActiveCell) = 0 Then
Rng.Rows®.EntireRow.Delete
End If
Next R

Call DeleteLoop

End Sub

Reply | Quote

Cindy: I agree with Hans, it is not at all clear how the macro should determine which rows are to be deleted.

Reply | Quote

Cindy: I agree with Hans, it is not at all clear how the macro should determine which rows are to be deleted.

Reply | Quote

It was a typo. I was looking at Column A instead of the actual row number when I was writing the post.

Reply | Quote

Why do you want to delete row 22 and 23, but not 31 and 32?
Why do you want to delete row 39, but not 35 – 37?

Reply | Quote

Here is a sample. I need to delete rows 22 – 30 and 39 – 69.

Reply | Quote

The test for CountA = 0 tests that there are NO non-blank cells in a row. From your description, I gather that column A is always populated, so the count would never be 0. Cells with formulas don’t count as blanks either. Can you try to describe more precisely what you want, and perhaps attach a small demo worksheet?

Reply | Quote

I think this is close to what you need…once you post an example I can help some more….
Sub DeleteEmptyRows()

Dim R As Long
Dim C As Range
Dim Rng As Range

Range(“B1”).Select
Do
If Not (ActiveCell) = 0 Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until (ActiveCell) = 0

If (ActiveCell) = 0 Then
Set Rng = Selection
Else
Set Rng = ActiveSheet.UsedRange.Rows
End If
For R = Rng.Rows.Count To 1 Step -1
If (ActiveCell) = 0 Then
Rng.Rows®.EntireRow.Delete
End If
Next R

Reply | Quote

If you change range B1 to M1 it will work, but you’d have to keep running the Macro (setting a macro button or something). I don’t know how to loop it, and haven’t the time to figure it out right now, sorry.

Reply | Quote

If you change range B1 to M1 it will work, but you’d have to keep running the Macro (setting a macro button or something). I don’t know how to loop it, and haven’t the time to figure it out right now, sorry.

Reply | Quote

Jeremy: Have you actually tried your code? I haven’t, but it does not look like it will work very well. First, your code does a lot of cell selecting and then working with the ActiveCell (like recorded macro code does). This is almost always NOT a good way to do things. First, it causes the screen to flash while the macro is running, and second it is very slow. If there are very many cells involved, the code will run a long time.

Second, you have this loop in your code:

For R = Rng.Rows.Count To 1 Step -1
    If (ActiveCell) = 0 Then
        Rng.Rows®.EntireRow.Delete
    End If
Next R

That code tests the ActiveCell to see if it is zero and then deletes a row if it is and does nothing if it is not. There is nothing in the loop to change the ActiveCell to a different cell. So, if the ActiveCell is not zero when the loop starts, nothing will be deleted since the ActiveCell will never change. If the ActiveCell is zero when the loop starts, then all rows will be deleted since the ActiveCell does not change and it will remain zero throughout the loop.

Reply | Quote

First, to all, I appreciate the criticism, this is my 2nd day ever typing macros….in response to Legare’s Comment;

Do
If Not (ActiveCell) = 0 Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until (ActiveCell) = 0

My code works, I just don’t have a variable for column b being blank.

Reply | Quote

It may work in one particular case, but there are many cases where that code will NOT do what you expect it to do and delete rows that were not ment to be deleted.

Reply | Quote

It may work in one particular case, but there are many cases where that code will NOT do what you expect it to do and delete rows that were not ment to be deleted.

Reply | Quote

Thanks for the help, I’m trying to absorb all this in and decipher it on my own, I’m taking a class on this but it isn’t as in-depth as this…I’m trying this problem just to test myself and push myself to learn more….I have a question….

What exactly does this do: Count To 1 Step -1

Reply | Quote

    For R = Rng.Rows.Count To 1 Step -1

This line starts R at the count of the number of rows in the range Rng. So, if the range Rng contains 10 rows, the value of R starts at 10. Then each time through the loop, one is subtracted from R (the Step -1). When R reaches one (the To 1), the loop is ended. Basically, that is looping backward through the range from the last row in the range to the first. This is usually done when a loop is deleting rows so that when a row is deleted, it does not alter which row is looked at next.

Reply | Quote

So Rng.Rows.Count To 1 Step -1 Is looking at the current row, and then moving on? or backwards through the entire document, going row 1 then to row 65536, then to 65535, and so on?

Reply | Quote

That line of code does not look at anything, it just sets the value of R to a number (the number of rows in the range Rng), and then decrements it by one each time through the loop. Other code within the loop would have to use R as an index to look at or otherwise act on a cell or range of cells. No, it does not go from 1 to 65536. The To 1 stops it when R is decremented to less that 1. Therefore, the loop would stop when R is less than 1 (the last time through the loop, R would be 1). R is just an integer number, it is not a range in this case. It can be used as an index or an offset to reference a row, but it is not a range.

Reply | Quote

Thanks for all of the help. I sent out a pre-designed spreadsheet with over 2,000 rows to the field offices. In Colum A I have numbered the rows. In column B the customer name is input. They then fill in the forecast for twelve months beginning with January in Column C and the total in column O. Even though I give each office 50 rows, per salesperson, to hold their data, they may only use 25 rows. Basically, if Column B is blank, then I need the row deleted. When the spreadsheet is completed and sent back to me, I give a copy to someone else in the office. They want the data condensed into a smaller spreadsheet with the blank rows deleted. With 125 spreadsheets coming back to me, I wanted a quick way to delete the blank rows from this large spreadsheet. Thank you.

Reply | Quote

Test the code in post 431845 with the red section removed and the ‘Then’ moved up to the previous line. (As soon as I edit it to turn it red. ) Post back if it isn’t what you are looking for.

Reply | Quote

Test the code in post 431845 with the red section removed and the ‘Then’ moved up to the previous line. (As soon as I edit it to turn it red. ) Post back if it isn’t what you are looking for.

Reply | Quote

See if this will do what you want:

Public Sub DelEmptyRows()
Dim I As Long
    With Worksheets("Sheet1")
        For I = .Range("A65536").End(xlUp).Row - 1 To 3 Step -1
            If (.Range("A1").Offset(I, 0).Value  "" And IsNumeric(.Range("A1").Offset(I, 0).Value) _
              And .Range("B1").Offset(I, 0).Value = "") Then
                .Range("A1").Offset(I, 0).EntireRow.Delete
            End If
        Next I
    End With
End Sub

Reply | Quote

See if this will do what you want:

Public Sub DelEmptyRows()
Dim I As Long
    With Worksheets("Sheet1")
        For I = .Range("A65536").End(xlUp).Row - 1 To 3 Step -1
            If (.Range("A1").Offset(I, 0).Value  "" And IsNumeric(.Range("A1").Offset(I, 0).Value) _
              And .Range("B1").Offset(I, 0).Value = "") Then
                .Range("A1").Offset(I, 0).EntireRow.Delete
            End If
        Next I
    End With
End Sub

Reply | Quote

Thanks for all of the help. I sent out a pre-designed spreadsheet with over 2,000 rows to the field offices. In Colum A I have numbered the rows. In column B the customer name is input. They then fill in the forecast for twelve months beginning with January in Column C and the total in column O. Even though I give each office 50 rows, per salesperson, to hold their data, they may only use 25 rows. Basically, if Column B is blank, then I need the row deleted. When the spreadsheet is completed and sent back to me, I give a copy to someone else in the office. They want the data condensed into a smaller spreadsheet with the blank rows deleted. With 125 spreadsheets coming back to me, I wanted a quick way to delete the blank rows from this large spreadsheet. Thank you.

Reply | Quote

That line of code does not look at anything, it just sets the value of R to a number (the number of rows in the range Rng), and then decrements it by one each time through the loop. Other code within the loop would have to use R as an index to look at or otherwise act on a cell or range of cells. No, it does not go from 1 to 65536. The To 1 stops it when R is decremented to less that 1. Therefore, the loop would stop when R is less than 1 (the last time through the loop, R would be 1). R is just an integer number, it is not a range in this case. It can be used as an index or an offset to reference a row, but it is not a range.

Reply | Quote

So Rng.Rows.Count To 1 Step -1 Is looking at the current row, and then moving on? or backwards through the entire document, going row 1 then to row 65536, then to 65535, and so on?

Reply | Quote

    For R = Rng.Rows.Count To 1 Step -1

This line starts R at the count of the number of rows in the range Rng. So, if the range Rng contains 10 rows, the value of R starts at 10. Then each time through the loop, one is subtracted from R (the Step -1). When R reaches one (the To 1), the loop is ended. Basically, that is looping backward through the range from the last row in the range to the first. This is usually done when a loop is deleting rows so that when a row is deleted, it does not alter which row is looked at next.

Reply | Quote

Thanks for the help, I’m trying to absorb all this in and decipher it on my own, I’m taking a class on this but it isn’t as in-depth as this…I’m trying this problem just to test myself and push myself to learn more….I have a question….

What exactly does this do: Count To 1 Step -1

Reply | Quote

Jeremy, a VBA book such as those by John Walkenbach will help you along the learning curve.

Reply | Quote

Jeremy, a VBA book such as those by John Walkenbach will help you along the learning curve.

Reply | Quote

First, to all, I appreciate the criticism, this is my 2nd day ever typing macros….in response to Legare’s Comment;

Do
If Not (ActiveCell) = 0 Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until (ActiveCell) = 0

My code works, I just don’t have a variable for column b being blank.

Reply | Quote

Jeremy: Have you actually tried your code? I haven’t, but it does not look like it will work very well. First, your code does a lot of cell selecting and then working with the ActiveCell (like recorded macro code does). This is almost always NOT a good way to do things. First, it causes the screen to flash while the macro is running, and second it is very slow. If there are very many cells involved, the code will run a long time.

Second, you have this loop in your code:

For R = Rng.Rows.Count To 1 Step -1
    If (ActiveCell) = 0 Then
        Rng.Rows®.EntireRow.Delete
    End If
Next R

That code tests the ActiveCell to see if it is zero and then deletes a row if it is and does nothing if it is not. There is nothing in the loop to change the ActiveCell to a different cell. So, if the ActiveCell is not zero when the loop starts, nothing will be deleted since the ActiveCell will never change. If the ActiveCell is zero when the loop starts, then all rows will be deleted since the ActiveCell does not change and it will remain zero throughout the loop.

Reply | Quote

I think this is close to what you need…once you post an example I can help some more….
Sub DeleteEmptyRows()

Dim R As Long
Dim C As Range
Dim Rng As Range

Range(“B1”).Select
Do
If Not (ActiveCell) = 0 Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until (ActiveCell) = 0

If (ActiveCell) = 0 Then
Set Rng = Selection
Else
Set Rng = ActiveSheet.UsedRange.Rows
End If
For R = Rng.Rows.Count To 1 Step -1
If (ActiveCell) = 0 Then
Rng.Rows®.EntireRow.Delete
End If
Next R

Reply | Quote