How do you delete a row in excel using vba based on several cells

[Naomi72]

Hi,

I'm trying to delete rows in an excel spreadsheet using vba based on the fact that all the cells in a row in columns l -> r contain 0.

Many thanks!!

 

[SkipVought]

Have you done a search in Tek-Tips for delete rows. LOTS of stuff out there.

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Naomi72]

HI Skip,

I've spent qute a while sifting through the results on delete rows, and picked up quite a few interesting tips, however none addressed my dilemma, all seven cells in that row must contain 0 for me to delete the row. Any ideas ?

Many thanks!!

 

[SkipVought]

Naomi,

then on each row you must loop thru those 7 columns to test the values

  dim bZero as boolean
...
   bZero = True
   for iCol = 1 to 7
      if cells(thisrow, iCol).value <> 0 then 
        bZero = False
        exit for
      end if
   next
   if bZero then cells(thisrow, 1).entirerow.delete
...

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Loomah]

Hi
Just checked before posting this to see if I'd been beaten but a whole conversation??!!!!

Anyway, slightly different approach that avoids looping the columns. Not a massive difference as there are only 7 columns. Note cells must be 0, not blank or empty.

Sub a()
Dim lRow As Long
For lRow = Range("A65536").End(xlUp).Row To 1 Step -1
    If WorksheetFunction.CountIf(Range(Cells(lRow, "L"), _
        Cells(lRow, "R")), 0) = 7 Then
        Rows(lRow).EntireRow.Delete
    End If
Next
End Sub

Happy Friday
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[xlbo]

nice sideways step Loomah - here's another:

Sub a()
Dim lRow As Long
For lRow = Range("A65536&quot.End(xlUp).Row To 1 Step -1
If Not Range("L" & lRow & ":R" & lRow).Find(0, LookIn:=xlValues, lookat:=xlWhole) Is Nothing Then
Rows(lRow).EntireRow.Delete
End If
Next
End Sub


Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[SkipVought]

Naomi,

Lots of ways to skin a cat. If the entire row is empty then Looma or xlbo's will work well.

If you are looking at specific columns where other data is don't care, then you'll have to do SOMETHING like mine.



Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[xlbo]

Actually Skip - mine looks at cols L to R only but won't delete if there's any blanks etc - will only work if all 7 columns have a 0 in - as will Loomah's

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[Naomi72]

Thank you all, very helpful, I'll try them out in my code.

Many thanks!!

 

[Loomah]

Skip
??????
I don't understand what you're saying. My code works on the basis the each cell on a given row between columns L & R contains "0". Not on blank rows (for which I use the CountA function!!)
If there are blanks or any other value in those 7 columns the row is skipped(!)
It does the same as yours but without the loop!

Happy Friday
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[xlbo]

you need to get a quicker connection Loomah

Have a good weekend - hope you beat the Gooners !!

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[SkipVought]

SORRY guys!

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[SkipVought]

I'm jugglin' too many balls.

Fell down on this one!

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Loomah]

xlbo
This most might arrive by Monday!
I don't think your code fits this MO as it deletes rows that contain 0 not rows that contain only 0 in the 7 columns.

Skip
Leave your balls alone!

Happy Friday
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[SkipVought]

ooooooooooooooooooooohhhhh my!

Why didn't I think of that!

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[PHV]

Loomah, from Original Post:
all the cells in a row in columns l -> r contain 0
No mention about another cell in the row.
Naomi72 , what about an AutoFilter on this 7 columns ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[xlbo]

Loomah - absolutely correct - my logic got all confused - too many double negatives - I was trying to be clever and failed ;-)

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[Loomah]

PHV
I don't know what you're trying to tell me. Unless I'm just being dim coz it's Monday and I'm ****** ***!!

phoespen
A word of warning. Using SUM in this specific case may not work. The MO is to check 7 cells in each row to see that they all contain 0. Let's assume those cells contain
10, 5, 987, -7, -900, -3 & -92
The SUM would be 0 but none of the cells would actually contain 0. It's an extreme case but always a possibility!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[PHV]

Loomah, I apologize for my ignorance of the english grammar.
I was confused with the word only.