Code to select rows and delete

rmobley232

New Member
Joined
Jun 30, 2016
Messages
15
Hello!

Can you please help with with this code where I am trying to delete certain amount of rows after I have applied a filter selection criteria. The range of rows (in the example, rows 50:54) will change on a daily basis. :

Rows("50:54").Select
Selection.Delete Shift:=xlUp
ActiveWorkbook.Worksheets("Results").AutoFilter.Sort.SortFields.Clear

Thanks so much for the assistance!!
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Are Rows("50:54") the rows that are visible after you have filtered the data?
 
Upvote 0
Yes, they are visible after I have filtered the data and therefore theoretically become the 2nd row in the data set, after the header row.
 
Upvote 0
What is the range that you are filtering? Are you filtering manually or by using a macro? If by a macro, could you please post the code?
 
Upvote 0
Try with this:

Code:
    u = Range("A" & Rows.Count).End(xlUp).Row
    If u = 1 Then
        MsgBox "No rows to delete"
    Else
        Rows("2:" & u).Delete shift:=xlUp
    End If
    
    ActiveWorkbook.Worksheets("Results").AutoFilter.Sort.SortFields.Clear
 
Upvote 0
Mumps,

This is the code I am using to filter the data, which I need to update to anything that starts with a K:

ActiveSheet.Range("$A$1").AutoFilter Field:=3, Criteria1:=Array( _
"K07", "K09", "K35"), Operator:=xlFilterValues
 
Upvote 0
Please post your full code as you are using Field:=3.
 
Upvote 0
anything that starts with a K

If the below doesn't work for you then as requested in my previous post then post your full code.

Code:
Sub Filterit()
    With Worksheets("Results").Range("A1:C" & Worksheets("Results").Range("C" & Rows.Count).End(xlUp).Row)
        .AutoFilter 3, "K*"

        On Error Resume Next
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(12).EntireRow.Delete
        On Error GoTo 0
        .AutoFilter

    End With
End Sub
 
Upvote 0
Mumps,

This is the code I am using to filter the data, which I need to update to anything that starts with a K:

ActiveSheet.Range("$A$1").AutoFilter Field:=3, Criteria1:=Array( _
"K07", "K09", "K35"), Operator:=xlFilterValues


Then :

Code:
    u = Range("[COLOR=#0000ff]K[/COLOR]" & Rows.Count).End(xlUp).Row
    If u = 1 Then
        MsgBox "No rows to delete"
    Else
        Rows("2:" & u).Delete shift:=xlUp
    End If
    
    ActiveWorkbook.Worksheets("Results").AutoFilter.Sort.SortFields.Clear

Try and tell me.
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,163
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