How to remove rows from list in excel by vba

M

m_vishal_c

Board Regular
Joined
Dec 7, 2016
Messages
209
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
hi I have 2 sheets(Sheet1, Need to be removed) in Excel workbook. i need to remove all rows from Sheet1 where column name contains list from sheet "Need to be removed".
for that i am using below code but it does not delete all rows.
for example. in Sheet1, there are 10 rows of Person name "abc" when i run this code it does not delete all 10 rows. I got these below code from internet and i am new to macro. Please help me.

Code: 
Dim iListCount As IntegerDim iCtr As Integer


' Turn off screen updating to speed up macro.
Application.ScreenUpdating = False


' Get count of records to search through (list that will be deleted).
iListCount = Sheets("Sheet1").Range("A1:A5000").Rows.Count




' Loop through the "master" list.
For Each x In Sheets("Need to be removed").Range("A2:A30")
' Loop through all records in the second list.
For iCtr = 1 To iListCount
' Do comparison of next record.
' To specify a different column, change 1 to the column number.
If x.Value = Sheets("Sheet1").Cells(iCtr, 1).Value Then
' If match is true then delete row.
Sheets("Sheet1").Cells(iCtr, 1).Delete xlShiftUp
' Increment counter to account for deleted row.
iCtr = iCtr + 1
End If
Next iCtr
Next
Application.ScreenUpdating = True
MsgBox "Done!"

Please guide me. much appreciate your time and support
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Sort by date Sort by votes
.
Code: 
Option Explicit


Sub RowDel()
    Dim lr As Long
    Application.ScreenUpdating = False    '\/Change number 1 below to correspond to affected column.
    lr = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row '<----- Change to actual sheet name
    With Range("A1:A" & lr)                                   '<-- Change to corresponding start row
        .Replace "abc", "", xlWhole                           '<-- Currently checking for abc. Edit as required
        .SpecialCells(4).EntireRow.Delete
    End With
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
hi Logit,

thanks for replying. Here you mentioned .replace "abc" but in my case these value is matched with second sheet.
May be I explained differently
sorry for inconvenience
for example: in sheet1 below are the data

NameAddressAge Post
abc111
xyz111
abc111
ddd111
mmmm111
xyz111

<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>
</tbody>

and "Need to be removed" sheets shows
A
Name
abc
xyz

<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>

data. sheet1 will remove all data where "Need to be removed " data is match in sheet1.
If i fail to make you understand then please let me know

thanks
 
Upvote 0
.


A​
B​
C​
D​
E​
F​
G​
H​
1​
NameAddressAgePost
2​
abc
1​
1​
1​
<--- This is in Sheet 1
3​
xyz
1​
1​
1​
4​
abc
1​
1​
1​
5​
ddd
1​
1​
1​
6​
mmmm
1​
1​
1​
7​
xyz
1​
1​
1​
8​
9​
10​
11​
Name<--- This in Sheet "Need To Be Removed"
12​
abc
13​
xyz
14​
15​
If the name appears in "Need To Be Removed" you want those
16​
names to be deleted from Sheet 1.
17​
18​
Is this correct ?
19​
 
Upvote 0
Logit said:
.


[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]E[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]F[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]G[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]H[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​
NameAddressAgePost
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​
abc
1​
1​
1​
<--- This is in Sheet 1
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​
xyz
1​
1​
1​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​
abc
1​
1​
1​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​
ddd
1​
1​
1​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR]​
mmmm
1​
1​
1​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]7[/COLOR]​
xyz
1​
1​
1​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]8[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]9[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]10[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]11[/COLOR]​
Name<--- This in Sheet "Need To Be Removed"
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]12[/COLOR]​
abc
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]13[/COLOR]​
xyz
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]14[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]15[/COLOR]​
If the name appears in "Need To Be Removed" you want those
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]16[/COLOR]​
names to be deleted from Sheet 1.
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]17[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]18[/COLOR]​
Is this correct ?
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]19[/COLOR]​

<tbody>
</tbody>
Click to expand...

yes, you are right
 
Upvote 0
.
No need to quote the previous post when you reply. It takes up extra space on the servers.

Here is a macro that will do the job for you :

Code: 
Option Explicit


Sub delete_selected_rows()
    Dim rng1 As Range, rng2 As Range, rngToDel As Range, c As Range
    Dim lastRow As Long


    With Worksheets("Sheet1")
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        Set rng1 = .Range("A2:A" & lastRow)
    End With


    Set rng2 = Worksheets("Sheet2").Range("A2:A100")


    For Each c In rng1
        If Not IsError(Application.Match(c.Value, rng2, 0)) Then
            'if value from rng1 is found in rng2 then remember this cell for deleting
            If rngToDel Is Nothing Then
                Set rngToDel = c
            Else
                Set rngToDel = Union(rngToDel, c)
            End If
        End If
    Next c


    If Not rngToDel Is Nothing Then rngToDel.EntireRow.Delete
End Sub
 
Upvote 0
Logit said:
.
No need to quote the previous post when you reply. It takes up extra space on the servers.

Here is a macro that will do the job for you :

Code: 
Option Explicit


Sub delete_selected_rows()
    Dim rng1 As Range, rng2 As Range, rngToDel As Range, c As Range
    Dim lastRow As Long


    With Worksheets("Sheet1")
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        Set rng1 = .Range("A2:A" & lastRow)
    End With


    Set rng2 = Worksheets("Sheet2").Range("A2:A100")


    For Each c In rng1
        If Not IsError(Application.Match(c.Value, rng2, 0)) Then
            'if value from rng1 is found in rng2 then remember this cell for deleting
            If rngToDel Is Nothing Then
                Set rngToDel = c
            Else
                Set rngToDel = Union(rngToDel, c)
            End If
        End If
    Next c


    If Not rngToDel Is Nothing Then rngToDel.EntireRow.Delete
End Sub
Click to expand...

Amazing, it works, heaps thanks
 
Upvote 0
hi Logit.

i have another question for which i have posted new. "[h=1]how to Filter particular / specific word and cut & paste in different sheet by vba".[/h]
is there any way i need to filter particular / specific word in particular column like("A column") and select all(Filtered data), cut and paste , create another sheet in same workbook and paste into it.

for example
Need to filter "MPS" in code column and move those filtered in another sheet by creating new sheet

odeaddressPart
PIT-00000001300083532611
TAP-00000001250256747011
2FRE-65-00-MPS-02011
ALF-0000000125025866611
ALF-00000001250261293711
2FRE-65-00-MPS-031|2FRE-6511
ALF-00000001250262197711
TAP-0000000150055721511

<tbody>
</tbody>


heaps thanks in advance

thanks
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
Print from database
Replies
0
Views
126
Charlreena
C
E
  • Solved
Delete Rows using input box to multiple sheets
Replies
4
Views
251
Elle_mostlysunny
E
J
  • Question
How to delete row on two worksheets
Replies
2
Views
313
jdcar
J
P
  • Question
ListBox Click to textbox & delete row
Replies
0
Views
326
pure vito
P
R
  • Solved
Add from multiselect listbox items with multiple columns to another list box using cmd buttons
Replies
6
Views
3K
John_w
J

Forum statistics

Threads
1,214,951
Messages
6,122,446
Members
449,083
Latest member
Ava19

Share this page

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