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