SolvedPrivate

Delete Excel Entire rows on conditions

Posted on 2015-02-20
16
12 Views
Last Modified: 2016-02-10
Been a WHILE since I worked in Macros

I have a table with varying numbers of rows
Can change from day to day when I copy in the data itself

Lets say this is an example

I need to cycle through all rows
Check COlumn B and Column C

If Row x Column B contains the text "Column"
or
If Row x Column C Integer value > 0

Delete the entire row

Focus on Ro1 1 column 1 on finish
0
Comment
Question by:lrbrister
  • 7
  • 6
  • 3
16 Comments
 
LVL 32

Expert Comment

by:Rob Henson
ID: 40621381
Have you looked at Autofilter?

Select your data and go to the Data menu and click Filter. This will add dropdowns to each column.

You can then specify the criteria for each column. Once all criteria are set and the visible rows show what you want to delete, select the whole block of data and delete rows as you would normally, only those visible rows will be deleted. Disable filter and the remaining rows will re-appear.

Each column criteria will be dealt with as an AND comparison. If you need OR comparisons, you will need to add a formula to each row to identify when criteria are met and then filter on that column instead.

Thanks
Rob H
0
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
ID: 40621389
on error resume next
for introw = 9999 to 1 step -1
  if cells(introw,2) like "*Column*" and cells(introw,3)>0 and int(cells(introw,3))=cells(introw,3) then
      rows(introw).delete
  end if
next
cells(1,1).select
0
 

Author Comment

by:lrbrister
ID: 40621484
Phillip...Thanks

Final Live solution

    Dim iii As Integer
    iii = Range("A3").End(xlDown).Row
   
    On Error Resume Next
    For introw = iii To 3 Step -1
    If Cells(introw, 7) Like "*Cover*" Or Cells(introw, 11).Value > 0 Then
      Rows(introw).Delete
    End If
    Next
 
    Cells(1, 1).Select
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40621488
OK - bear in mind that that will not test whether column K is an integer or not.
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 40621497
Did you try the Filter option?
0
 

Author Comment

by:lrbrister
ID: 40621599
I know

Rob...

This had to be run in a Macro

The data has to be removed automatically

Phillip
They are responsible for making sire the data is clean
Solution works perfectly
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 40625432
A filter routine can be incorporated into a VBA routine.
0
 

Author Comment

by:lrbrister
ID: 40625597
Rob...
Ok...My point is that this information will be copied and pasted elsewhere.

Don't the "hidden" rows go along with that?

Also...
In VBA...an example in your post would have had me look at that first
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40625601
If rows are filtered, and copied and pasted, the rows which are hidden will not be pasted.
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 40625607
Until now, you have wanted the rows deleted. The requirement to copy elsewhere changes things somewhat.
0
 

Author Comment

by:lrbrister
ID: 40625703
My question clearly stated "Deleted"...not "Hidden"
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 40625751
Yes it does, so are you wanting to copy what is left after the rows have been deleted?
0
 

Author Comment

by:lrbrister
ID: 40625761
Rob,
 I wasn't looking to do anything other than what I placed in the question.

Delete the rows.
Delete means...well...delete.

Phillips answer does that.

It doesn't hide...
It deletes

Thanks for your follow-up
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 40625927
OK, I was just looking at the "bigger picture" and the end result rather than just this step in the process.

If you are wanting the remaining data extracted elsewhere, you might also be able to use the Advanced Filter option, which can also be included in VBA if so required and can copy the results of the filter to another sheet.

Thanks
Rob H
0
 

Author Comment

by:lrbrister
ID: 40625947
I'll keep that in mind for my next question when it happens.
0
 

Author Closing Comment

by:lrbrister
ID: 40636379
Thanks

Thought I had awarded this already.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now