VBA Excel (variable range) help

[chrizzler]

Hi there, been puzzling over this one to the point of destruction!

Can someone help.....please!!!

For Each c In Range("b67:fe67")

If c.Value < 200 Then
c.EntireColumn.Hidden = True
Else
c.EntireColumn.Hidden = False
End If

Next

Ok, so there's my basic code.

I have a ss with a load of data on, the last row being the totals. I need to hide any columns that are less than 200 which my code does. The problem I have is that the data changes daily and therefore the end row can be different, therefore B67 can easily be B100, so I need some way of finding the last row and putting this into my code.

Something like

For Each c In Range("bx:fex")

where x is the number of the row.

Any ideas what I can do?

Thanks in advance

[Bob Phillips]

[vba]

LastRow = Cells.Find(What:="*", _
After:=Range("A1"), _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row

For i = 67 To LastRow

Num = Application.CountIf(Cells(i, "B").Resize(, 5), "<200")
Rows(i).Hidden = Num > 0
Next i
[/vba]

[chrizzler]

that didn't seem to work? I don't even undertand it to try and manipulate it though!

more info:-

so this last row (which can be on any row) looks like this:-

grand total | 100 | 200 | 300 | 10 | 400 | 500 | 123 | 199

what it needs to do is hide any columns on the grand totals row less than 200

[Bob Phillips]

What did it do for you? It seemed to work in my tests.

[chrizzler]

it hides the whole row of data, whereas it needs to hide individual columns based on their total value being less than 200

[Bob Phillips]

You can't hide individual cells, it is either rows or columns.

[chrizzler]

its columns i want to hide (if the value on the grand total row is less than 200)

[Bob Phillips]

Sorry, I messed that up

[vba]

LastRow = Cells.Find(What:="*", _
After:=Range("A1"), _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row

For i = 2 To 6

Num = Application.CountIf(Cells(67, i).Resize(LastRow - 66), "<200")
Columns(i).Hidden = Num > 0
Next i
[/vba]

[chrizzler]

hehe, thanks for trying, it's better, but still not right.

it correctly hides columns if grand total is on row 67 and a few more beyond, but if grand total is on row 50, it errors out "run time error 1004".

[Bob Phillips]

You original code started on row 67. Are you now saying that you want it in any row?

[chrizzler]

my original code said it was row 67, but I explained that was the problem, the grand total can be on any row. My original code worked if it was only row 67!

[Bob Phillips]

So what happens if the Grand Total is in row 50 say, and in $20 there is a number < 200, should column B be hidden, or should it just be testing ONLY that very last row.

[chrizzler]

its only the grand total row numbers we need to concentrate on, there will be a lot of numbers in the other rows preceeding the grand total (the last column will be a sum of these numbers). Any number in any column on the grand total row <200 should be hidden. And this grand total row could be between row 5 and row 1000 .

[mdmackillop]

Assuming "grand total" in column A
[vba]
Sub HideCols()
Dim cel As Range, c As Range
Set cel = Columns(1).Find("grand total")
For Each c In Range(cel.Offset(, 1), Cells(cel.Row, Columns.Count).End(xlToLeft))
If c < 200 Then c.EntireColumn.Hidden = True
Next
End Sub
[/vba]

[chrizzler]

oh thanks so much, that works perfectly every time

really appreciate it and thanks xld for trying.

Chrizzler

[mdmackillop]

Now you can see the importance of getting the question clear and concise.

XLD did all the work.