Delete Specific Rows

N

Nomas

Board Regular
Joined
Jun 14, 2011
Messages
91
Hi All,

I am looking for a macro that will delete all specified rows in a worksheet based upon what is in column A. If the row shows "Upload" (which is sorted to the top of the sheet) I want to keep it. If the row shows "No Change" I want to delete it. You can see below when I recorded the macro that It was row 5 I selected the delete all below that. It wont always be row 5, but it will be rows that say "No Change", with them sorted it should always be at the bottom.

Sub UploadSort()
'
' UploadSort Macro
'

'
Cells.Select
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Rows("2:2").Select
Application.CutCopyMode = False
Selection.AutoFilter
ActiveWorkbook.Worksheets("Item Upload Template").AutoFilter.Sort.SortFields. _
Clear
ActiveWorkbook.Worksheets("Item Upload Template").AutoFilter.Sort.SortFields. _
Add Key:=Range("A2"), SortOn:=xlSortOnValues, Order:=xlDescending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Item Upload Template").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Rows("5:5").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
Range("A1").Select
End Sub
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
Hello,
add this snippet of code at the end of your sub.

Code: 
    Dim LR As Long, i As Long
    
    LR = Range("A" & Rows.Count).End(xlUp).Row
    
    For i = LR To 1 Step -1
        If Range("A" & i).Value = "No Change" Then
            Range("A" & i).EntireRow.Delete
        End If
    Next i
 
Upvote 0
Thanks for the quick reply, I think I put it in the wrong spot though. When I run this I get an error on Selection.Autofilter, I am assuming I am doing something wrong.





Sub UploadSort()
'
' UploadSort Macro
'

'
Cells.Select
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Rows("2:2").Select
Application.CutCopyMode = False
Selection.AutoFilter
ActiveWorkbook.Worksheets("Item Upload Template").AutoFilter.Sort.SortFields. _
Clear
ActiveWorkbook.Worksheets("Item Upload Template").AutoFilter.Sort.SortFields. _
Add Key:=Range("A2"), SortOn:=xlSortOnValues, Order:=xlDescending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Item Upload Template").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Dim LR As Long, i As Long

LR = Range("A" & Rows.Count).End(xlUp).Row

For i = LR To 1 Step -1
If Range("A" & i).Value = "No Change" Then
Range("A" & i).EntireRow.Delete
End If
Next i
End Sub
 
Upvote 0
Hi, I'm not too sure what's wrong with it so try recording your macro again and appending my code at the end.
 
Upvote 0
Thanks KPark, I took your advice and it seems to be moving along but when I modified the Macro to this, it runs, but it takes a LOOOOOOOOOOONGGG time to run, as in many many minutes and still doesnt finish. Is there a way to mod this so it will sort the information and the remove it faster?

Sub UploadSort()
'
' UploadSort Macro
'

'
Sheets("Item Upload Template").Select
Cells.Select
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Dim LR As Long, i As Long

LR = Range("A" & Rows.Count).End(xlUp).Row

For i = LR To 1 Step -1
If Range("A" & i).Value = "No Change" Then
Range("A" & i).EntireRow.Delete
End If
Next i
End Sub
 
Upvote 0
Hi Nomas,

Yes, deleting rows upwards will take a long time for large datasets and/or complex workbooks. Which row contains your headings?

Robert
 
Upvote 0
Hey Trebor,

The rows I want to keep have the word "Upload" in column A, the rows I want to delete have "No Change" in column A. I think if there was a way to do the sort that puts the "Upload" on top which I have in the macro, and then delete from the first "No Change" down, that would be ideal. The range has a fixed length and width (BZ12000 is the max, the full is A3:BZ12000) and it will always be column A that has one of those 2 values (Upload or No Change). Thanks for the help, its much appreciated! :)
 
Upvote 0
Hi just wanted to bump to see if anyone had any further suggestions? I appreciate any guidance. :)
 
Upvote 0
You didn't answer my question re which row(s) contain the column headings. As such, the code assumes they're in the first two rows (we need to know so the rows are hidden or elese they could be deleted):

Code: 
Option Explicit
Sub Macro1()

    Dim strMyCol As String
    Dim varFilterItem As Variant
    Dim lngLastRow As Long
    Dim blnAutoCalc As Boolean
    
    strMyCol = "A" 'Column containing relevant data.  Change to suit.
    varFilterItem = "No Change" 'Can be a string or numeric. Change to suit.
    
    lngLastRow = Cells(Rows.Count, strMyCol).End(xlUp).Row
    
    With Application
        If .Calculation = xlCalculationAutomatic Then
            blnAutoCalc = True
            .Calculation = xlCalculationManual
        End If
        .ScreenUpdating = False
    End With
    
    With ActiveSheet
        .AutoFilterMode = False
        .Columns(strMyCol).AutoFilter Field:=1, Criteria1:=varFilterItem
        .Rows("1:2").EntireRow.Hidden = True 'Hide the heading rows so they're not deleted
        .Columns(strMyCol).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        .AutoFilterMode = False
        .Rows("1:2").EntireRow.Hidden = False 'Unhide heading rows.
            With Application
                If blnAutoCalc = True Then
                    .Calculation = xlCalculationAutomatic
                    .ScreenUpdating = True
                End If
            End With
        .Range(strMyCol & "2").Select
    End With

End Sub

Note, as this macro will delete rows, make sure you initially run it on a copy of your data in case the results are not as expected.

Regards,

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

Similar threads

G
  • Question Question
Sort (Z-A) Macro not working
Replies
1
Views
85
NateSC
NateSC
S
  • Solved
Delete rows before a certain time
2 3
Replies
26
Views
862
Joe4
Joe4
E
  • Solved
A little VBA/Macro code adjustment
Replies
3
Views
286
rollis13
rollis13
H
  • Solved
Sorting and Deleting VBA for large file
2
Replies
16
Views
796
heathball
H
S
  • Question Question
Help with combining 2 macros into 1
Replies
8
Views
269
MARK858
MARK858

Forum statistics

Threads
1,224,614
Messages
6,179,906
Members
452,949
Latest member
beartooth91

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