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?
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,945
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,675
Messages
5,833,073
Members
430,188
Latest member
EG93

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
Top