PDA

View Full Version : VBA Excel (variable range) help



chrizzler
03-19-2009, 09:01 AM
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
03-19-2009, 09:19 AM
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

chrizzler
03-19-2009, 09:33 AM
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
03-19-2009, 09:45 AM
What did it do for you? It seemed to work in my tests.

chrizzler
03-19-2009, 09:50 AM
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
03-19-2009, 09:52 AM
You can't hide individual cells, it is either rows or columns.

chrizzler
03-19-2009, 09:53 AM
its columns i want to hide (if the value on the grand total row is less than 200)

Bob Phillips
03-19-2009, 10:13 AM
Sorry, I messed that up



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

chrizzler
03-19-2009, 10:19 AM
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
03-19-2009, 10:28 AM
You original code started on row 67. Are you now saying that you want it in any row?

chrizzler
03-19-2009, 10:30 AM
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
03-19-2009, 10:41 AM
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
03-19-2009, 12:59 PM
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
03-19-2009, 01:13 PM
Assuming "grand total" in column A

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

chrizzler
03-19-2009, 01:18 PM
oh thanks so much, that works perfectly every time :clap:

really appreciate it and thanks xld for trying.

Chrizzler

mdmackillop
03-19-2009, 01:24 PM
Now you can see the importance of getting the question clear and concise.

XLD did all the work.