VBA solution - Range?

chrizzler

New Member
Joined
Mar 18, 2009
Messages
6
Hi there,

Long time lurker, but have finally come up against a brick wall. I'm sure it's a simple solution that I can't work out!

Here's my code:-

Code:
 Sub RemoveColumns()

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

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

Next

End Sub

Now, the problem is that the row number within the range is always changing, so my B67:FE67, could easily be B99:FE99, so I'm manually editing it each time. I'm sure there's a way it could be done so that this changes automatically, but I don't know how?!

I have one constant that will always be the bottom row that has the data in that it checks to see if it's below 200 and that is "grand total"

Any ideas anyone?
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
it's basically always the line with grand total on, here it checks to see if the value generated is less than 200, if it is, then it needs to hide the column, otherwise it moves on.

more often than not, this is the bottom row, but not quite always!
 
Upvote 0
If you can logically explain a procedure to find the row you need the code run on, then code can be written for it. If it's not something you can logically figure out, then chances are that code can't do it. Writing the code is the easiest part -- thinking of what the code needs to do is the hard part.
 
Upvote 0
how about find the line that grand total appears on and go from there, but I don't know how to do that in VBA terms and embed it into the row number.

I guess something like

find grandtotal, what's the row number = x

then run my code but instead of having a number i would use Bx : FE x
 
Upvote 0
I'm not in front of a computer with Excel at the moment, so I can't test the code, but you should look at using .Find to find "Grand Total" in the sheet, and then set that to a range. Use .row of that range to find the row, and use that to loop through with c.
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top