delete row in excel using macro

August 4, 2010 at 11:02:16
Specs: Windows XP

I have a data table and need to delete rows based on certain criteria. If in the "ID Number" column (column d) there are cells that equal "123" or "456" then the entire row needs to be deleted. How can this be done?...I keep running into a run time error 1004.

See More: delete row in excel using macro

Report •


#1
August 4, 2010 at 11:24:12

Hi,

Please post your code.

Use the pre icon above the reply box - this creates <pre> and </pre> tags. Put you code between the tags.

Regards


Report •

#2
August 4, 2010 at 11:35:23

Option Explicit

Sub Delete()
    Dim i As Integer
    Dim datacolumn As Integer
    Dim rulerow As Integer
    Dim datarow As Long
    Dim total As Integer
    
    rulerow = 2
    datarow = 2
    total = 0
    

For i = Range("d65536").End(xlUp).Row To 1 Step -1
    If Cells(datarow, i).Value = ("123") Then
        Rows(i - 1).Resize(1).Delete
    ElseIf Cells(datarow, i).Value = ("456") Then
        Rows(i - 1).Resize(1).Delete
        
    
Do
        total = total + 1
    Loop Until IsEmpty(Worksheets("Report").Cells(total, 2))
    total = total - 2

    

End If

 Next
   
End Sub


Report •

#3
August 4, 2010 at 11:38:38

I am completely new to Visual Basic and found a template online and tried to follow it...my apologies if it is incoherant code.

Report •

Related Solutions

#4
August 4, 2010 at 12:19:07

Hi,

Here is a macro that will delete rows that contain either 409008256 or 210057052 in column D:

Option Explicit

Sub DeleteRow()
Dim rngStart As Range
Dim rngEnd As Range
Dim n As Integer

'set start of data in column D
Set rngStart = Range("D2")
'find end of data in column D
Set rngEnd = Range("D" & CStr(Application.Rows.Count)).End(xlUp)

'work from end of data as deleting rows alters row numbers
For n = rngEnd.Row To rngStart.Row Step -1
    'test value in column D
    If Range("D" & CStr(n)).Value = 409008256 Or _
                Range("D" & CStr(n)).Value = 210057052 Then
        'matching value - so select whole row and delete
        Range("D" & CStr(n)).EntireRow.Delete
    End If
Next n
End Sub

In your macro there were a few issues:
1. The name of the macro matched the name of an existing function (Delete), so it was not accepted.
2. You were testing Cells(datarow, i).Value, but datarow was set to 2 and was never changed and i was counting through rows, but was used as a column value - Cells(row,column).
3. In your test for the numbers you had this - Cells(datarow, i).Value = ("409008256"). This means you were looking for text not an actual number. Also there is no need to place the number in brackets. The use of double quotes denotes that what is inside them is text, even if they look like a number.
4. The delete line - Rows(i - 1).Resize(1).Delete was selecting a single row. The Rows() statement means that you select a single row from the collection of rows on the worksheet - it doesn't select n rows it selects Row n
Then you resized it to one row.
5. Resize uses Resize(Rows,Columns). Whilst it is OK to use the default value of 1 for columns, I prefer to explicitly state the value, then I am less likely to miss the fact that I didn't want 1 column
6. I got a bit lost on what you were trying to achieve with the Do Loop Until part of the macro. I did notice that you used:
Cells(total, 2) which refers to cells in column B, and I wasn't clear why you needed to use column B.

Overall it would help you to document what each part of the code does. It is useful when debugging, but also later if you need to change the macro, it is great to be reminded what the code was doing, rather than trying to work it out from scratch.

Regards
PS - I just saw your response
It helps me understand how you ended up with that code :)


Report •

#5
August 4, 2010 at 12:26:06

Thank you Humar!! you are a life saver. Code works perfectly. And I can add in more numbers via "or" command correct? Thank you again.

Report •

#6
August 4, 2010 at 12:28:04

Are you sure that you are getting an run Time Error 1004?

I can't even compile the code because it's named Delete which is a Reserved word.

re: my apologies if it is incoherant code

Pretty much!

You've got variables declared (and initialized) that aren't even used in the code:

    
    Dim datacolumn As Integer
    Dim rulerow As Integer

    rulerow = 2

In addition, you determine how many rows are in Column D with:

i = Range("d65536").End(xlUp).Row 

but then you use i as the column argument (not the row argument) for the Cells method.

Cells(datarow, i)

That means you are searching Row 2, column by column, not searching Column D row by row.

I have no idea (based on your stated requirements) what this section is for:

Do
        total = total + 1
    Loop Until IsEmpty(Worksheets("Report").Cells(total, 2))
    total = total - 2

Is deleteing rows that contain 123 or 456 in Column D the only thing the code is supposed to do?



Report •

#7
August 4, 2010 at 12:42:33

Hi,

Yes, you can add more numbers with further OR operators.

If there were going to be a lot, I would do the address calculation once like this:

    'test value in column D
    'setup address text first
    strAddr = "D" & CStr(n)
    If Range(strAddr).Value = 409008256 Or _
                Range(strAddr).Value = 210057052 Or _
                Range(strAddr).Value = 123456789 Then

You will also need to add this near the beginning:
Dim strAddr As String

Regards


Report •

#8
August 4, 2010 at 12:55:09

I wonder which is more efficient...the

     If this Or _
        this Or _
        this Then"

method or Select Case:

   Select Case Range("D" & CStr(n))
      Case 409008256, 210057052, 123456789
        'matching value - so select whole row and delete
        Range("D" & CStr(n)).EntireRow.Delete
    End Select

If nothing else, Select Case might make it easier to add/modify/delete values to the list.


Report •

#9
August 4, 2010 at 15:00:07

Hi DerbyDad03,

Yes Select Case looks easier.

No idea if there is any performance difference ...

Regards

Humar


Report •


Ask Question