delete entire row through Macro buttong and VB code

anis_fuelindia

Board Regular
Joined
Aug 8, 2007
Messages
231
Hi All
How can we delete entire row using VB Code.

say example i want to delete a row having name 'Anis' by clicking on a buttton

is this possible?

please help

Thanks

Anis
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Anis

In order for people to help you, you must be as specific as possible. Bear in mind that no one but you knows your project so you need to describe exactly what you want to achieve, otherwise folk will do work to help you out, then will need to do more when you come back and explain what the ACTUAL problem is.

You said that you wish to delete a row "Having the name Anis."

Is the row actually named "Anis" (i.e. have you declared a named range?), or do you have a list of names down a column (col "A" perhaps?), with associated data accross the rows (Records), and what you want to do is find a specific name in the column, and delete the whole record from the table.
I'll assume that this is what you're trying to do:
As long as the button's from the Control Toolbox, right_click it, select "View Code" and make the whole code look thus:

Code:
Private Sub CommandButton1_Click()
Dim nme As String

On Error GoTo err_handler

nme = InputBox("Please type the name of person who's record you wish to delete", "Record delete facility!")
If nme = "" Then Exit Sub

    If MsgBox("You have chosen to delete " & nme & "'s record from the database!" & Chr(10) & "Is this correct?", 52, "DELETING RECORD!!") = vbNo Then MsgBox "Nothing has been deleted.": Exit Sub

Me.Rows(Application.WorksheetFunction.Match(nme, Me.Range("A1:A20"), 0)).EntireRow.Delete
MsgBox nme & "'s entire record has been deleted!"
Exit Sub

err_handler: MsgBox "The name was not found in the database!" & Chr(10) & "Nothing has been deleted.!"
End Sub
This assumes that:
1. Your button is called CommandButton1.
2. Your list of names is contained within A1:A20, which it almost definitely won't be, so you'll have to change this reference to suit, BUT ALWAYS BEGIN WITH ROW 1, NOT THE NUMBER OF THE ROW YOUR DATA STARTS ON, ELSE THE WRONG ROW WILL BE DELETED.

Also, as records are deleted, so the range may have to change - it depends how volatile your "database" is.
You could just go completely over the top, and overestimate how many records you'll be using, so if you only ever expect there to be 25, then use the range A1:A50, to be on the safe side. It would be much better coding to dynamically find the ACTUAL existing range, and define the data that way, but as I'm not at all sure if this is what you want, then I'm not going down that road for you.
 
Upvote 0
delete entire row through Macro button and VB code

Hi Sykes

From next time onwards will see that i would be clear on what i needed.

And thanks for the code as this is working fine, the only 1 addition thing i wanted is.

when we run this code it only delete one row at a time.

is it possible to delete mutiple rows having same same.

example if i am having 4 row having name 'Anis'
A1=Anis
A2-Anis
A3=Anis
A4=Anis

and if i run the code it should delete all the four row at once.

Thanks in advance

Anis
 
Upvote 0
EDIT***

Try this:

will replace the word Anis with an X which you can filter and then delete all X's. Set your range accordingly if needed.

Code:
Sub find()
For Each c In Worksheets(1).Range("A:A")
  If (c.Value) Like "Anis" Then
    c.Offset(0, 0).Value = "X"
  End If
Next
End Sub

Or try this: EDIT*** You must highlight your range to be checked first.

Code:
Sub DeleteRows()
    Dim strToDelete As String
    Dim rngSrc As Range
    Dim NumRows As Integer
    Dim ThisRow As Integer
    Dim ThatRow As Integer
    Dim ThisCol As Integer
    Dim J As Integer
    Dim DeletedRows As Integer

    strToDelete = InputBox("Value to Trigger Delete?", "Delete Rows")
    Set rngSrc = ActiveSheet.Range(ActiveWindow.Selection.Address)

    NumRows = rngSrc.Rows.Count
    ThisRow = rngSrc.Row
    ThatRow = ThisRow + NumRows - 1
    ThisCol = rngSrc.Column

    For J = ThatRow To ThisRow Step -1
        If Cells(J, ThisCol) = strToDelete Then
            Rows(J).Select
            Selection.Delete Shift:=xlUp
            DeletedRows = DeletedRows + 1
        End If
    Next J
    MsgBox "Number of deleted rows: " & DeletedRows
End Sub
 
Upvote 0
Hi,

Thanks for ur help

but please let me know where do i need to define the range?
i copy and pasted the above given code and when run the code it ask me first for 'Value to Trigger Delete?' and when i type the value that i wanted to delete say 'Anis' it give another popup saying Number of deleted row: 0

please let me know where i need to do the edition ?
Thanks
Anis
 
Upvote 0
if Anis is in column A, highlight all your data in column A, then run Macro.
 
Upvote 0
Hi Anis

Glad it's working for you.

This should do what you wish, now:
Code:
Private Sub CommandButton1_Click()
Dim nme As String
Dim cll As Range, rng As Range
Dim rcrd As Integer
Set rng = Sheets("sheet1").Range("A4:A20")
rcrd = 0


nme = InputBox("Please type the name of person who's records you wish to delete", "Record delete facility!")
If nme = "" Then Exit Sub

    If MsgBox("You have chosen to delete " & nme & "'s records from the database!" & Chr(10) & "Is this correct?", 52, "DELETING RECORDS!!") = vbNo Then MsgBox "Nothing has been deleted.": Exit Sub

    For Each cll In rng
        If cll.Value = nme Then
            Me.Rows(cll.Row).EntireRow.Delete
            rcrd = rcrd + 1
        End If
    Next
    
    If rcrd > 0 Then
        MsgBox rcrd & " of " & nme & "'s records have been deleted!"
    Else
        MsgBox "There were no records matching that name." & Chr(10) & "No records have been deleted."
    End If

End Sub

Hope this helps.......
 
Upvote 0
Hi Sykes

When i run the code it says ' There are no record matching that name and no record deleted'

although in that sheet i have given 'Anis' on 3 rows from A1 till A3.

but the code couldnt trace the name.

please help?

Thanks

Anis
 
Upvote 0
Presumably you need to delete different names at different times.

Do you really need a macro?

If you have a large number of rows, the codes suggested so far will be slow (and some of the code suggested will not necessarily delete all required rows).

It is quite simple to do it manually :

- select column A

- go to Data>AutoFilter

- filter for "Anis" (or whatever)

- select from the first filtered cell to the first blank cell below the last filtered cell

- go to Edit>GoTo>SpecialCells>VisibleCellsOnly>OK

- go to Edit>Delete>EntireRow

If you wish, the Filter can be left there for future deletions (otherwise, remove it by Data>AutoFilter)

If you really need a macro, no doubt someone can provide one based on the steps set out above (or a search of the board will provide plenty of code examples that do exactly that - for example, RichardSchollar's code at http://www.mrexcel.com/board2/viewtopic.php?t=288598&highlight=delete+rows).
 
Upvote 0
delete entire row through Macro button and VB code

Hi Boller

Thanks for your advice, but since i am having an ample of data so its not that easy to delete some particular row of a particular name.

Thanks for zour solution too...

have a nice time...

Thanks
Anis
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

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