Help with hiding columns
 

I have two questions

1) Is there a built-in VBA or Excel function that returns the Column Letter
for any given cell?

2) I would like to know the best way to hide/unhide columns using VBA. I
wrote a For...Next loop using the Sheet.EntireColumn.Hidden = True and it
worked fine until I went to unhide it. It gave me the generic 1004 error.

Any help would be appreciated!

Help with hiding columns
 

Demo'd from the immediate Window:

cells(20,"AA").Activate
? Left(ActiveCell.Address(0,0),1 - (ActiveCell.Column 26))
AA
Cells(3,"B").Select
? Left(ActiveCell.Address(0,0),1 - (ActiveCell.Column 26))
B

Sub BBCC()

For i = 1 To 10
If i Mod 2 = 0 Then
Columns(i).Hidden = True
End If
Next

MsgBox "Take a Look"

For i = 1 To 10
If i Mod 2 = 0 Then
Columns(i).Hidden = False
End If
Next

End Sub

works fine for me.

--
regards,
Tom Ogilvy

"ExcelDave" wrote in message
...
I have two questions

1) Is there a built-in VBA or Excel function that returns the Column
Letter
for any given cell?

2) I would like to know the best way to hide/unhide columns using VBA. I
wrote a For...Next loop using the Sheet.EntireColumn.Hidden = True and it
worked fine until I went to unhide it. It gave me the generic 1004 error.

Any help would be appreciated!

Help with hiding columns
 

Hello ExcelDave,

In response to your first question, no there is not a built-in Excel o
VBA function to return the Column letter. You can easily write a macr
to do it. Add a Module to your VBA project and paste this code into it
The macro can be called in VBA or used as a Worksheet function.

VBA Examples:
To return the Column of the Active Cell...
Ltr = ColumnLetter()

To Return the Column of specific Cell...
Ltr = Column(BT256)

Worksheet Examples:
To Return the Column of the Active Cell...
=ColumnLetter()

To Return the Column of a specific Cell...
=Column(BT256)

Macro Code:

Public Function ColumnLetter(Optional Address As Range) As String

Dim Addx

If Address Is Nothing Then
Addx = ActiveCell.Address(True, False)
Else
Addx = Address.Address(True, False)
End If

ColumnLetter = Left(Addx, InStr(1, Addx, "$") - 1)

End Function

Sincerely,
Leith Ros

--
Leith Ros
-----------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...fo&userid=1846
View this thread: http://www.excelforum.com/showthread.php?threadid=54572

Help with hiding columns
 

Thanks to Tom and Leith for your suggestions! You sure sabved me a lot of
time experimenting.

"Leith Ross" wrote:


Hello ExcelDave,

In response to your first question, no there is not a built-in Excel or
VBA function to return the Column letter. You can easily write a macro
to do it. Add a Module to your VBA project and paste this code into it.
The macro can be called in VBA or used as a Worksheet function.

VBA Examples:
To return the Column of the Active Cell...
Ltr = ColumnLetter()

To Return the Column of specific Cell...
Ltr = Column(BT256)

Worksheet Examples:
To Return the Column of the Active Cell...
=ColumnLetter()

To Return the Column of a specific Cell...
=Column(BT256)

Macro Code:

Public Function ColumnLetter(Optional Address As Range) As String

Dim Addx

If Address Is Nothing Then
Addx = ActiveCell.Address(True, False)
Else
Addx = Address.Address(True, False)
End If

ColumnLetter = Left(Addx, InStr(1, Addx, "$") - 1)

End Function

Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=545727