VBA code for automatic deletion of rows

srinivasarv

Board Regular
Joined
Mar 25, 2008
Messages
62
Hi,

My data is in the following format,

Company Name Bill Status
GTAEPL ACCEPTED
GACEPL REJECTED

I need a vba code if the in Bill status column rejected is there all those rows to be deleted.

Thanks & Regards,
Srinivas
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Hi,

My data is in the following format,

Company Name Bill Status
GTAEPL ACCEPTED
GACEPL REJECTED

I need a vba code if the in Bill status column rejected is there all those rows to be deleted.

Thanks & Regards,
Srinivas

Why VBA code

It Can be use by simple AUTO FILTER option
 
Upvote 0
This code assumes that "Rejected" is NOT all upper case and is in column B:
Code:
Sub DeleteRejectedRow()
    Dim rng As Range, rng2 As Range
    Set rng = Range("B65536").End(xlUp)
    Do Until rng.Row = 2
        Set rng2 = rng.Offset(-1)
        If rng = "Rejected" Then rng.EntireRow.Delete
        Set rng = rng2
    Loop
End Sub
 
Upvote 0
A macro without a loop, might be faster for you if the dataset is lengthy:
Code:
Sub DeleteRejected()
Dim lastrow As Long
lastrow = Range("B" & Rows.Count).End(xlUp).Row

With ActiveSheet
    .Cells(1, 2).AutoFilter
    .Range("B1").AutoFilter Field:=1, Criteria1:="Rejected"
    .Range("B2:B" & lastrow).SpecialCells(xlCellTypeVisible).EntireRow.Delete (xlShiftUp)
    .AutoFilterMode = False
End With
End Sub
 
Last edited:
Upvote 0
hi jbeaucaire,

I need few changes to the code, the Bill status is in Column D and the data is starting from row 2, can u pl change the code.

This is a huge data hence i need yr help.

Thanks & Regards,
Srini
 
Upvote 0
Try this:
Rich (BB code):
Sub DeleteRejected()
Dim lastrow As Long
lastrow = Range("D" & Rows.Count).End(xlUp).Row

With ActiveSheet
    .Cells(1, "D").AutoFilter 
    .Range("D1").AutoFilter Field:=1, Criteria1:="Rejected"  ' D1 says "Bill Status", right?
    .Range("D2:D" & lastrow).SpecialCells(xlCellTypeVisible).EntireRow.Delete (xlShiftUp)
    .AutoFilterMode = False
End With
End Sub

Did you look at the code, the letters to change sort of leap out at you, don't they?
 
Upvote 0
I just ran it again on a sheet. It runs and gives no errors. Care to provide any more insight into what you're seeing? "It's breaking" is phenomenally vague.
 
Upvote 0
HI,

When i run this macro it is comming and stoping at this line and giving msg run time errorr.

.Range("D2:D" & lastrow).SpecialCells(xlCellTypeVisible).EntireRow.Delete (xlShiftUp)

Can u pl help to solve this problem
 
Upvote 0
Does the code begin the Autofilter?
Are there "Rejected" rows visible?

If not:
Do you have text such as "Rejected" or is it "REJECTED"?
Adjust the code to match the text on your page.
 
Upvote 0

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top