hide rows with macro


G

Guest

i want a looping macro that evaluates two columns in each row of the
spreadsheet and if both cell contain zeros hide the row. Basically if col b
or col c is zero hide row otherwise don't hdie and move down one row to
evaluate it,
 
Ad

Advertisements

C

CLR

You can do this with the Autofilter............whether you want to delete
rows with one zero or two, or whatever........

Data > Filter > AutoFilter > Custom > "does not equal" > 0.........then if
you want to further massage that result, just copy and paste it over to
another worksheet............

Vaya con Dios,
Chuck, CABGx3



"Macro to hide rows in spreadwsheet" <Macro to hide rows in spreadwsheet
@discussions.microsoft.com> wrote in message
news:[email protected]
 
J

JE McGimpsey

You give conflicting criteria - "both cells contain zeros" and "col b or
col c is zero"

Which do you mean?

"Macro to hide rows in spreadwsheet" <Macro to hide rows in
 
Ad

Advertisements

G

Guest

I've done this before by putting a formula in a column in the spreadsheet to
evaluate whether or not the row should be hidden, then referencing that
column in the code.

So let's say you put this formula in column "Q":

=IF(AND(B49=0,C49=0),"Y","N")

Then in your macro:

For rowNum = 49 To 778
If Range("Q" & rowNum) = "Y" Then
Range("Q" & rowNum).Select
Selection.EntireRow.Hidden = True
End If
Next rowNum

Make sure you unhide all the rows before calculating the sheet and running
this macro.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top