Re-Alphabetize list with hidden rows

[Elaine]

I have a custom list of letters A-Z which enables me to type an 'a' in cell
E3 and drag down the fill handle to get the other characters. However, when
rows are hidden I would like to ignore hidden cells and re-alphabetize just
the visible cells.

I am able to do this with numbers with the following recorded macro:
Sub mcrRenumVisCells()
'Renumbers visible cells -- ignores hidden cells
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay,
Step:=1, Trend:=False
End Sub

Is there anything I can do adapt this for my alphabets? Thanks.

 

[Jim Cone]

Elaine,

The following code will work on a single column or row with hidden cells.
Also, I could not get your number macro to work for me.
'--------------------------------------------------
Sub MakeVisCellsAlpha()
Dim rngCell As Excel.Range
Dim rng As Excel.Range
Dim lngNum As Long
lngNum = 65

Set rng = Selection.SpecialCells(xlCellTypeVisible)
For Each rngCell In rng
rngCell.Value = Chr$(lngNum)
If lngNum >= 90 Then
lngNum = 65
Else
lngNum = lngNum + 1
End If
Next 'rngCell
Set rngCell = Nothing
Set rng = Nothing
End Sub
'--------------------------------------------------

Regards,
Jim Cone
San Francisco, USA

I have a custom list of letters A-Z which enables me to type an 'a' in cell
E3 and drag down the fill handle to get the other characters. However, when
rows are hidden I would like to ignore hidden cells and re-alphabetize just
the visible cells.
I am able to do this with numbers with the following recorded macro:

 

[Elaine]

Thank you very much, Jim. If I could impose upon you a bit more:
I see that you have Chr(65) etc and I think that it is ingenious. However,
if one wanted a list from a..az for instance (it would become aa after z has
been reached) how does one do that?

Your macro is obviously very nice in that I don't even have to enter an 'a'
in the first cell. In the number macro that I included with my first note, I
have to enter a '1' in the first cell and then select the area that should be
numbered and then run the macro.

Your macro is so useful that I should modify your macro to work with numbers.

When I posed this question I was not optimistic about getting a reply as I
had checked a half-dozen books, the google website and consulted with several
people more technically proficient than myself and could not get anything
close. I really appreciate you taking the time to provide an answer to this
question. It is going to prove most useful.

--Elaine

 

[Jim Cone]

Elaine,

Hope this one is useful too.

Question: ..."if one wanted a list from a..az for instance (it would become

aa after z has been reached) how does one do that?"...

'----------------------------------------------------------
'The following code will work only on a single column or row.
'Returns A to Z, then AA to AZ, then BA to BZ etc. in visible
' cells in the selection. Calls function GetColumnLetters
'Jim Cone - San Francisco, USA - March 04, 2005
'----------------------------------------------------------
Sub AddAlphasToVisibleCells()
Dim rngCell As Excel.Range
Dim rng As Excel.Range
Dim lngNum As Long
lngNum = 1

Set rng = Selection.SpecialCells(xlCellTypeVisible)
For Each rngCell In rng
rngCell.Value = GetColumnLetters(lngNum)
lngNum = lngNum + 1
If lngNum > 256 Then lngNum = 1
Next 'rngCell
Set rngCell = Nothing
Set rng = Nothing
End Sub

'------------------------------------------------------------------
' Thanks to Chip Pearson
' Returns the address of the column from the provided column number.
'------------------------------------------------------------------
Function GetColumnLetters(ByVal ColumnNum As Long) As String
On Error GoTo NoColumn
Dim ColChars As String
ColChars = Columns(ColumnNum).Address(False, False)
GetColumnLetters = Left$(ColChars, 2 + CBool(ColumnNum < 27))
Exit Function
NoColumn:
Beep
GetColumnLetters = vbNullString
End Function
'------------------------------------------------------------------

Regards,
Jim Cone
San Francisco, USA