Help create macro to delete rows in list

[bishop7262]

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?

 

[Trebor76]

Hi bishop7262,

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

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]

bishop7262,


Sample data before the macro:

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

After the macro:

Excel Workbook
	A
1	Name
2	bishop7262
3	hiker95
4	Peter
5	Mike
6	Carol
7	Sue
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.

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]

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]

bishop7262,


Sample data before the macro:

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

After the macro:

Excel Workbook
	A
1	Name
2	bishop7262
3	hiker95
4	Peter
5	Mike
6	Carol
7	Sue
8	bishop7262
9	hiker95
10	Peter
11	Mike
12	Carol
13	Sue
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).

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.

 

[bishop7262]

you are awesome!!!

thank you

 

[hiker95]

bishop7262,

Thanks.

 

[bishop7262]

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

 

[hiker95]

bishop7262,

You should start a new post with your current request.


And:

Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4 (contains graphic instructions).
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php

Or, you can upload your workbook to www.box.net and provide us with a link to your workbook.