Delete Specific Rows

[Nomas]

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

 

[Jonmo1]

Spotted another typo in the other thread...
Keyboard is evil today.

Try

Sub UploadSort()
Dim LR As Long, FR As Long
With Sheets("Item Upload Template")
    .UsedRange.Copy
    .Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    FR = .Range("A:A").Find("No Change").Row
    .Range("A" & FR & ":A" & LR).EntireRow.Delete
End With
End Sub

 

[Nomas]

Richard,

First let me say I am sorry if I wasted anyone's time, my intention of the other thread was that I was hoping for a response today and after trying to bump it and noticing no responses and that you were in Australia (I figured this thread was buried for you to respond to and I didnt want to get naggy by continuing to bump) so I created a new thread. My apologies for that. I see that you did ask for rows, I think in my head I heard columns since that is what I was looking to sort. The headers are in row 2. I ran your code and it worked like a gem. Thank you again for your time. With what this will allow me to do you and the other awesome members have saved me weeks (if not months of work).

Jonmo I did try yours (most recent) and it did work, thanks for sticking with me. I am curious if these 2 macros are just a different approach to the same thing or if they are doing something different? I'll assume they do the same thing. To you both (and kpark if they are still following) sorry for the run around. Cant wait to get this tool running. Again you have all been great.

Regards,

John

 

[Trebor76]

Thanks for that and I'm glad it all worked out.

Regards,

Robert (aka Richard)

 

[Jonmo1]

Jonmo I did try yours (most recent) and it did work, thanks for sticking with me. I am curious if these 2 macros are just a different approach to the same thing or if they are doing something different? I'll assume they do the same thing.

Glad to help out....

No, they do not do the same thing.
In this case they get the same results because of assumptions made.
You stated the data was sorted, and all rows having "No Change" in column A are at the bottom.

So what my code does is

This line finds the Last Used Row in Column A
LR = .Range("A" & Rows.Count).End(xlUp).Row

This line finds the first (and ONLY the first) row that has "No Change" in Column A
FR = .Range("A:A").Find("No Change").Row

Then this line just deletes every row from FR to LR.
Regardless of what data is in Column A.
.Range("A" & FR & ":A" & LR).EntireRow.Delete


It just so happens to delete all rows with "No Change" in Column A because you have it sorted so those rows are at the bottom.


Hope that clears it up.

 

[Nomas]

Doh!

Thanks again Robert

Jonmo

Awesome, I ll definitely file these away.