Help create macro to delete rows in list

bishop7262

Board Regular
Joined
Jan 9, 2010
Messages
87
I have a column with around 500 names. I need to have a macro created where it will delete rows with names beginning with SR-Name

Right now I have to manually find all SR- and delete those rows. Is there anyway to create a macro to automatically delete those rows?
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,637
Hi bishop7262,

Try this (firstly on a copy of your data in case of unexpected results):

Code:
Sub Macro1()
        
    '//Declare variables//
    Dim lngRowStart As Long, _
        lngLastRow As Long, _
        lngActiveRow As Long
    Dim strMyCol As String
    Dim rngDelRange As Range
    
    '//Set variables//
    lngRowStart = 2 'Initial data row.  Change to suit.
    strMyCol = "A" 'Column containing relevant data.  Change to suit.
    lngLastRow = Cells(Rows.Count, strMyCol).End(xlUp).Row
    
    Application.ScreenUpdating = False
        
    For lngActiveRow = lngRowStart To lngLastRow
        If Left(Cells(lngActiveRow, strMyCol), 3) = "SR-" Then
            'Cater for initial setting of 'rngDelRange' range
            If rngDelRange Is Nothing Then
                Set rngDelRange = Cells(lngActiveRow, strMyCol)
            Else
                Set rngDelRange = Union(rngDelRange, Cells(lngActiveRow, strMyCol))
            End If
        End If
    Next lngActiveRow
        
    'If the 'rngDelRange' range has been set, then...
    If Not rngDelRange Is Nothing Then
        '...delete the rows within it.
        rngDelRange.EntireRow.Delete xlShiftUp
    'Else...
    Else
        '...inform the user that no rows are to be deleted as there was no _
        matching criteria in the dataset.
        MsgBox "No rows to be deleted as no there was no matching criteria"
    End If
    
    Application.ScreenUpdating = True
        
End Sub
Regards,

Robert
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
bishop7262,


Sample data before the macro:


Excel Workbook
A
1Name
2bishop7262
3hiker95
4SR-bishop7262
5Peter
6SR-hiker95
7Mike
8SR-hiker95
9Carol
10SR-bishop7262
11Sue
12
Sheet1





After the macro:


Excel Workbook
A
1Name
2bishop7262
3hiker95
4Peter
5Mike
6Carol
7Sue
8
9
10
11
12
Sheet1





Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Adding the Macro
1. Copy the below macro, by highlighting the macro code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.


Code:
Option Explicit
Sub DeleteSR()
' hiker95, 08/10/2010, ME483921
Dim LR As Long, Rcnt As Long
Application.ScreenUpdating = False
ActiveSheet.AutoFilterMode = False
LR = Cells(Rows.Count, "A").End(xlUp).Row
With Range("A1:A" & LR)
  .AutoFilter Field:=1, Criteria1:="=SR-*", Operator:=xlAnd
  Rcnt = 0
  Rcnt = Range("A1:A" & LR - 1).SpecialCells(xlCellTypeVisible).Count
  If Rcnt > 1 Then
    .Offset(1).SpecialCells(12).EntireRow.Delete
  End If
  .AutoFilter
End With
Application.ScreenUpdating = True
End Sub

Then run the "DeleteSR" macro.
 

bishop7262

Board Regular
Joined
Jan 9, 2010
Messages
87
Awesome hiker!!! That worked. But I just realized that some of the SR has a space; example sr - bishop7262

How would you change that to include those?

thank you
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649

ADVERTISEMENT

bishop7262,


Sample data before the macro:


Excel Workbook
A
1Name
2bishop7262
3hiker95
4SR-bishop7262
5Peter
6SR-hiker95
7Mike
8SR-hiker95
9Carol
10SR-bishop7262
11Sue
12bishop7262
13hiker95
14SR -bishop7262
15Peter
16SR -hiker95
17Mike
18SR -hiker95
19Carol
20SR -bishop7262
21Sue
22
Sheet1





After the macro:


Excel Workbook
A
1Name
2bishop7262
3hiker95
4Peter
5Mike
6Carol
7Sue
8bishop7262
9hiker95
10Peter
11Mike
12Carol
13Sue
14
15
16
17
18
19
20
21
22
Sheet1





Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


Code:
Option Explicit
Sub DeleteSR()
' hiker95, 08/10/2010, ME483921
Dim LR As Long, Rcnt As Long
Application.ScreenUpdating = False
ActiveSheet.AutoFilterMode = False
LR = Cells(Rows.Count, "A").End(xlUp).Row
With Range("A1:A" & LR)
  .AutoFilter Field:=1, Criteria1:="=SR-*", Operator:=xlOr, Criteria2:="=SR -*"
  Rcnt = 0
  Rcnt = Range("A1:A" & LR - 1).SpecialCells(xlCellTypeVisible).Count
  If Rcnt > 1 Then
    .Offset(1).SpecialCells(12).EntireRow.Delete
  End If
  .AutoFilter
End With
Application.ScreenUpdating = True
End Sub

Then run the updated "DeleteSR" macro.
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649

ADVERTISEMENT

bishop7262,

Thanks.
 

bishop7262

Board Regular
Joined
Jan 9, 2010
Messages
87
Don't know if this is possible but wondering if you could tell me if this can be done with a macro.

I have 2 columns, Trainer and Client

I run a filter and sort by Trainer. Right now I highlight the clients associated with that trainer and copy those clients then go to "[Trainer Name]" Worksheet and paste those clients in Column A Row 10. I was wondering if a macro can be built to bring up a pop up menu and allow me to do what I manually do.

So I can pick which trainer either by button which runs the macro and it automatically pasted those clients in the appropriate worksheet.

So far I have 6 trainers that has 6 worksheets that I manually go through and copy and paste clients.

thank you for your help in advance
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,912
Messages
5,514,122
Members
408,983
Latest member
mlee13

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top