Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
table of contents
is not clear yet
"Mohamed" wrote: How can i make table of contents for 15000 row, for example year 2001 row 1 year 2002 row 5000 year 2003 row 10000 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
table of contents
is not clear yet Open your file in excel. Make a new sheet for the contents. Rename it 'CONTENTS'. Menus: Tools-Macro-Visual Basic Editor In top left panel, double click ThisWorkbook Paste the following code in right panel: sub MakeContents() dim entryRow as long dim dataRow as long dim lastYear as long dim thisYear as long dim lastRow as long entryRow = 1 lastYear = 0 lastRow = Sheets("MAIN").Range("A:A").Count for dataRow = 1 to lastRow thisYear = Sheets("MAIN").Cells( dataRow, 1).Value if thisYear<lastYear then Sheets("CONTENTS").Cells( entryRow, 1).Value = "Year " + _ Format( thisYear) Sheets("CONTENTS").Cells( entryRow + 1, 1).Value = dataRow lastYear = thisYear entryRow = entryRow + 3 endif next end sub replace anywhere I've put a sheet name of MAIN with the name of the sheet which contains your data. If the years are not held in column 1 (i.e. column A), then you will have to change the lines: lastRow = Sheets("MAIN").Range("A:A").Count - replace "A:A" thisYear = Sheets("MAIN").Cells( dataRow, 1).Value - replace ( dataRow, 1) - change the 1 to the correct column number close the visual basic editor in excel, go to tools-Macro-Macros... double click on the entry which has MakeContents Iain King |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Table of Contents? | Excel Discussion (Misc queries) | |||
TABLE OF CONTENTS | Excel Discussion (Misc queries) | |||
Table of contents | Excel Worksheet Functions | |||
table of contents | Excel Programming |