PDA

View Full Version : hide columns depend on cell values



alienscript
08-29-2009, 03:02 AM
Hi Excel experts,

I can't figure out where is the bug, but i think is the invalid value in resized range. What I want to do is to hide the column(s) when the cell value in H5 and H6 are both empty and iterate rightward to the last used column. If either cell's value in the 2 rows has a value, it won't hide that column.

For example, I will run this code when I place my cursor at cell A5 (which is part number A), and the same when I place cursor at cell A7 (which is part number B), i will click-run this macro.

I'm using XL 2000. Appreciate if someone could please help. Thanks again.



Sub HideCols()
Dim myRow As String, lastcol As Long, c As Integer
lastcol = Cells.Find("*", SearchOrder:=xlByColumns, LookIn:=xlValues, _
SearchDirection:=xlPrevious).EntireColumn.Column
myRow = Split(ActiveCell(1).Address(1, 0), "$")(1)

For c = 8 To lastcol
If Cells(myRow, c).Resize(2, 1).Value = "" Then
Cells(myRow, c).EntireColumn.Select
Selection.EntireColumn.Hidden = True
Else
End If
Next c
End Sub

MaximS
08-29-2009, 04:47 AM
try this:



Sub HideCols()
Dim lastcol As Long, c As Integer
lastcol = Cells.Find("*", SearchOrder:=xlByColumns, LookIn:=xlValues, _
SearchDirection:=xlPrevious).EntireColumn.Column

For c = 8 To lastcol
If Cells(5, c).Value = "" And Cells(6, c).Value = "" Then
Columns(c).EntireColumn.Hidden = True
End If
Next c
End Sub

GTO
08-29-2009, 05:29 AM
Greetings,

I see that Maxim already has a solution. Just as you mentioned running it from either looking at row 5 or 7, I would think that ActiveCell.Row would be easier than the Split?


Sub HideCols()
Dim myRow As Long
Dim rngLastCol As Range
Dim c As Integer

With Worksheets("main")
Set rngLastCol = .Cells.Find(What:="*", _
After:=.Cells(1, 1), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious)

If rngLastCol Is Nothing Then Exit Sub

myRow = ActiveCell.Row

For c = 8 To rngLastCol.Column
If .Cells(myRow, c).Value = vbNullString _
And .Cells(myRow + 1, c).Value = vbNullString Then
.Cells(myRow, c).EntireColumn.Hidden = True
End If
Next c
End With
End Sub


Mark