Re-Alphabetize list with hidden rows

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.

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



"Elaine" <[email protected]> wrote in message
news:[email protected]...
> 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.

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" wrote:

> 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
>
>
>
> "Elaine" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
>

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





"Elaine" <[email protected]> wrote in message
news:[email protected]...
> 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" wrote:
>> 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



>> "Elaine" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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.