Delete Specific Rows

Status
Not open for further replies.
N

Nomas

Board Regular
Joined
Jun 14, 2011
Messages
91
Hi,

I am looking to delete certain rows that have the words "No Change" in column A and to keep those that have the word "Upload" in column A (those are the only 2 values for the column). I sort the sheet so that Upload is at the top and am looking to delete the ones that say "No Change" at the bottom. The range is fixed for the lookup (A3:BZ12000) and it will always be column A that has one of those 2 values (Upload or No Change). Thanks in advance for the help, its much appreciated. The macro below runs slow and am looking for one that will basically find the first "No Change" and delete everything below it.



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
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Sort by date Sort by votes
considering you stated the data is sorted, and the rows you want deleted (ColA="No Change") are at the bottom...

Then you can try
LR = Range("A" & Rows.Count).End(xlUp).Row
FR = Range("A:A").Find("No Change").Row
Range("A: & FR & ":A" & LR).EntireRow.Delete


Hope that helps.
 
Upvote 0
Couple Questions

I'm not sure I know how/where to modify it so your code works when input. If I put it in as


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
FR = Range("A:A").Find("No Change").Row

For i = LR To 1 Step -1
If Range(Range("A: & FR & ":A" & LR).EntireRow.Delete).Value = "No Change" Then
Range("A: & FR & ":A" & LR).EntireRow.Delete
End If
Next i
End Sub

I get a compile error: Expected: list separator or ) on this line

"A: & FR & ":<<<<<RIGHT HERE>>>>>A" & LR

I quite new to the macro thing so if you could let me know if there is anything else I need to modify that would be great. Thanks for the help so far jonmo, I appreciate it :)
 
Upvote 0
I missed a quote in my post...

And there is no loop, it deletes all the rows at once..
Again, assuming your initial statements are true
Data is Sorted
All rows with "No Change" in Column A are at the Bottom.

Try
Code: 
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
 
Upvote 0
Thanks for help so far Jonmo,

When I run that script I get a Run-time error '1004'

Application-defined or object-defined error

here:

.Range("A:" & FR & ":A" & LR).EntireRow.Delete


Any ideas?
 
Upvote 0
Status
Not open for further replies.

Similar threads

S
  • Question Question
Change macro range from column A to B
2
Replies
10
Views
136
sachin483
S
F
  • Question Question
VBA Code for Named Ranges
Replies
2
Views
712
fishandtril
F
R
  • Solved
Filter data set to #N/A, skip step entirely if none
Replies
3
Views
308
RoryA
RoryA
T
  • Solved
Find #REF! errors in multiple tables and delete those rows
Replies
7
Views
308
Peter_SSs
Peter_SSs
M
  • Solved
Marco to delete rows with a future date
2
Replies
14
Views
311
MARK858
MARK858

Forum statistics

Threads
1,224,609
Messages
6,179,877
Members
452,949
Latest member
Dupuhini

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