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!

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!

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

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