Hide Row if column A<Column B

[amerifax]

I have a spread sheet with many rows. It was 12 columns. Two columns have a number in them. I want to hide rowes based on the two numberd columns.

Example: if column A is 45 and B is 65 hide A
A<B Hide the row that these values come from.

Bob

 

[ajetrumpet]

loop through the rows with a range object, check the value of the column with a value of the other column using offset() property, and get the HIDE action of the activecell property. record a macro to get the proper code, but it should be something like:

dim r as range

for each r in range("a1", range("a1").end(xldown))
   if r.value < r.offset(5 columns, 0 rows) then
      activecell.rows.currentrow.hide
   end if
next r

that's just sample. the 3rd row is not right, but use the help menu to find where the hide row action comes out at and what object the ROW property is part of.

 

[chergh]

Recording a macro isn't really going to help give you the code for this, apart from how to actually hide a row. It should be more like:

sub blah()

dim ws as worksheet
dim rng as range

set ws = thisworkbook.worksheets("SheetNameGoesHere")

for each rng in ws.range("a1", ws.range("A" & ws.rows.count).end(xlup))

   if rng.value < rng.offset(0,1).value then
      ws.rows(rng.row).hidden = true
   end if

next rng

end sub

 

[amerifax]

OK...The columns I am using are "I" and "J". If "J" is 50% of "I" or more then I want to hide that Row.
So if J*.50 is greater than I, Hide the current Row. I am checking Rows 3-1971.

How would I work this into the Macro?

Bob

 

[Brianwarnock]

Thought it was A and B and just <.

In this case just change the Range, Chergh calculated, to the given range and change the test.

Brian

 

[chergh]

sub blah()

dim ws as worksheet
dim rng as range

set ws = thisworkbook.worksheets("SheetNameGoesHere")

for each rng in ws.range("I3:I1971")

   if rng.value =< 0.5 * rng.offset(0,1).value then
      ws.rows(rng.row).hidden = true
   end if

next rng

end sub

 

[amerifax]

This is really new to me so I am trying to work through it

for each rng in ws.range("a1", ws.range("A" & ws.rows.count).end(xlup))

 if rng.value < rng.offset(0,1).value then

The you say change the range this is what I am assuming:
"for each rng in ws.range("I3", ws.range("I" & ws.rows.count).end(xlup))"

And this is the test:
"if rng.value < rng.offset(0,1).value then"

This offset will compare I to J because it is set to 0-Columns, 1-Row?? Or I should probably say I < J.

I think I got, so this would be correct then, right??
" If rng.Value * 0.5 < rng.Offset(0, 1).Value Then"

Thanks again for all your help!!!

Bob

 

[Brianwarnock]

You were presumably typing whilst Chergh was posting and can now see how to change the macro,
This
ws.range("A" & ws.rows.count).end(xlup)

is to calculate the lastrow in a spreadsheet when it is not known , as you knew the end of your range it was much simpler as shown in Chergh's new code.

Brian

Edit Offset(row.col) same as Cells

 

[amerifax]

Yeah we must of working at the same time. If I look at it long enough and poke around, I can normally figure a lot out on my own.

I did change the range from the new code, otherwise, this is what I ended up with:

Sub hide_row()
Dim ws As Worksheet
Dim rng As Range
Set ws = ThisWorkbook.Worksheets("Muni List")
For Each rng In ws.Range("I3:I1971")
   If rng.Value * 0.5 < rng.Offset(0, 1).Value Then 'change the '.5' based on what percentage you want to use
      ws.Rows(rng.Row).Hidden = True
   End If
Next rng
End Sub

I think I stated the percentage wrong before, I need to hide everything where (I * .5)< J

So now I have my next questions. Can I also add to this macro If I > 1 then run the "If rng."

Also is there a way to "UnHide" all the rows that the Macro Hides when I am done, besides selecting everything and UnHiding. Maybe some kind of a toggle?

Thanks again for all your help and patience!

Bob

 

[Brianwarnock]

You mean that you want another macro saying

rows("3:1971").entirerow.hidden = False

rather than just select the entire sheet and use the menus, why not.

When you say I>1 do you mean
If rng.value >1 then
If etc

No reason why not.

brian

 

[Brianwarnock]

BTW you could pass the % via another Cell, say I2

Sub hide_row()
Dim ws As Worksheet
Dim rng As Range
Dim dblPercent as double
dblPercent =Range("I2") / 100
Set ws = ThisWorkbook.Worksheets("Muni List")
For Each rng In ws.Range("I3:I1971")
If rng.Value * dblPercent < rng.Offset(0, 1).Value Then 'change the '.5' based on what percentage you want to use
ws.Rows(rng.Row).Hidden = True
End If
Next rng
End Sub

The orange shows the changes. This is better practice than changing the code

Brian

 

[amerifax]

Will this in a Macro by itself, unhide everything or show all the rows?

rows("3:1971").entirerow.hidden = False

When you say I>1 do you mean
If rng.value >1 then
If etc

Yeah. Sometimes I may need to look at the data different ways for different reasons...I'm working thru this for my boss. These are different questions that he had.

In this example:
Dim dblPercent as double
dblPercent =Range("I2") / 100

Would I put the actual percentage that I want to use into the cell I2 or one that I choose? How would I format it, .50 or 50%?

I will remember the "orange" that is much easier to see!

Thanks once again for your help. I am starting to understand some of this a little more.

Heather (for Bob)

 

[Brianwarnock]

sub unhide()
rows("3:1971").entirerow.hidden = False
end sub

Will unhide and thus show all of those rows.

as written enter 50 for 50%, but this was an example play with it to satisfy your requirements.

Best of luck , got to go now

Brian

 

[amerifax]

Brian,

Thanks once again for all your help!

Heather

 

[Brianwarnock]

Happy to help, especially those who are trying to help themselves , I have read Bob's other threads, or were they Heather's, gee life is confusing for us old guys.

Brian

 

[amerifax]

Usually the first original post is Bob then most after that are mine.

Heather.