VBA Excel (variable range) help

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

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

Code:

---

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

Code:

---

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

---

where x is the number of the row.

Any ideas what I can do?

Thanks in advance

[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]

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

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

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

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

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

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]

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".

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

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!

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.

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 .

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]

oh thanks so much, that works perfectly every time :clap:

really appreciate it and thanks xld for trying.

Chrizzler

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

XLD did all the work.