Excel Worksheet_activate hide columns 2

[wwgmr]

Hi all, I am trying to write a simple code on my worksheet that when I activate the worksheet it checks for blank cells and if they are empty it hides the columns. This is to neaten up my sheet. I want to keep columns A thru H and columns after L. My code looks like this.

Private Sub Worksheet_Activate()
Dim I as Integer
I = 9
Application.ScreenUpdating = False
Columns("I:L&quot.select
Selection.EntireColumn.hidden = True 'This line should set I:L hidden in the worksheet.
Do Until IsEmpty(Cells(4, I)) or I = 13 'Checking for empty Non Empty cells untill finds one. Starting cell should be I4 or Cells(4, I) I=9 to start.
Cells(4, I).EntireColumn.hidden = False
I = I + 1
Loop
End Sub

My problem when I run this is it makes all columns from A through L Hidden. I think it is do to the Cells(4, i) line. It should be refering to cell I4 fourth Row Column 9 Yet When I walk through it it seems to be pointing to different cell almost like Column 3 row 9 or 10 Not sure. Any help would be great.

Eric

 

[DerfLongshanks]

the do until test will be true every time it finds a blank cell so it will never excute the code to unhide the cells

Try this instead

Private Sub Worksheet_Activate()
Dim I As Integer
I = 9
' Application.ScreenUpdating = False
Columns("I&quot.Select
Do Until I = 13 'Just loop through all columns and then check for blank cells and hide them
If IsEmpty(Cells(4, I)) Then
Cells(4, I).EntireColumn.Hidden = True
End If
I = I + 1
Loop
End Sub

 

[RobBroekhuis]

or, even simpler:

for i=9 to 12
if isempty(cells(4,i)) then cells(4,i).entirecolumn.hidden=true
next i
Rob

 

[wwgmr]

Thank you all very much for answers, I still seem to be having problem now. If I have a formula that says if( false,"",other) in the cell I am testing with cells(4,I) the result should be in that cell "" I.E. blank. Will the VB see it as empty or will it detect the formula and then treat the cell as not empty? Is this why I am having this trouble? Also the cell range I am testing is I4:L4. The vb should be checking each of these cells moving left to right.


Thanks again.

 

[wwgmr]

Not sure if I came across clearly. The code still isn't working correctly. Is this because the tested cell is not empty but contains a Function that returns a value or "" empty cell depending on a Lookup if statment.

Hope this clears up my question in last post.

Thanks again.

 

[RobBroekhuis]

IsEmpty will be false if the cell contains a formula, even if that formula evaluates to an empty string. But

for i=9 to 12
if cells(4,i)="" then cells(4,i).entirecolumn.hidden=true
next i

should work for you.
Rob

 

[wwgmr]

Thanks alot for the help!