Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Re-Alphabetize list with hidden rows
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" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Re-Alphabetize list with hidden rows
Thank you very much, Jim. If I could impose upon you a bit mo
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" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Re-Alphabetize list with hidden rows
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" wrote in message ... Thank you very much, Jim. If I could impose upon you a bit mo 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" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Alphabetize sets of rows within a WB | Excel Discussion (Misc queries) | |||
How do I display a list of the rows or columns that I've hidden? | Excel Discussion (Misc queries) | |||
How do i alphabetize a list? | Excel Discussion (Misc queries) | |||
How do you alphabetize a list in xcel? | Excel Discussion (Misc queries) | |||
How to alphabetize my created list? | New Users to Excel |