Delete Excel Entire rows on conditions
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Phillip...Thanks
Final Live solution
Dim iii As Integer
iii = Range("A3").End(xlDown).Ro
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
Final Live solution
Dim iii As Integer
iii = Range("A3").End(xlDown).Ro
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
OK - bear in mind that that will not test whether column K is an integer or not.
Did you try the Filter option?
ASKER
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
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
A filter routine can be incorporated into a VBA routine.
ASKER
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
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
If rows are filtered, and copied and pasted, the rows which are hidden will not be pasted.
Until now, you have wanted the rows deleted. The requirement to copy elsewhere changes things somewhat.
ASKER
My question clearly stated "Deleted"...not "Hidden"
Yes it does, so are you wanting to copy what is left after the rows have been deleted?
ASKER
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
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
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
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
ASKER
I'll keep that in mind for my next question when it happens.
ASKER
Thanks
Thought I had awarded this already.
Thought I had awarded this already.
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