Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFill Row Numbers & Print Area
Hi, I'm a newbie with macros and need some help. I have a spreadsheet with
data extracted by a macro and I've inserted Column A which is blank. I need to include something in the macro to auto fill the row numbers in column A to the end of the data. The number of rows is changeable. Also I need something that will set the print area each time the macro is run but the print area changes each time as well. I don't need to actually print the document though, just set the area ready to print. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFill Row Numbers & Print Area
Hi sage -
1. Add these statements to your macro to number the rows: Set homeCell = Range("B1") '<<change to top, left-most data cell rowCnt = homeCell.CurrentRegion.Rows.Count For i = 1 To rowCnt homeCell.Offset(i - 1, -1) = i Next i 2. Add this statement to set the print range: ActiveSheet.PageSetup.PrintArea = homeCell.CurrentRegion.Address --- Jay "sage" wrote: Hi, I'm a newbie with macros and need some help. I have a spreadsheet with data extracted by a macro and I've inserted Column A which is blank. I need to include something in the macro to auto fill the row numbers in column A to the end of the data. The number of rows is changeable. Also I need something that will set the print area each time the macro is run but the print area changes each time as well. I don't need to actually print the document though, just set the area ready to print. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFill Row Numbers & Print Area
Thanks Jay, that worked beautifully....only one small problem, I need to
count the rows from row 2 (row 1 is headers). I changed the home cell to B2 but it counts one extra row at the bottom....how can I fix it? Thanks Sage "Jay" wrote: Hi sage - 1. Add these statements to your macro to number the rows: Set homeCell = Range("B1") '<<change to top, left-most data cell rowCnt = homeCell.CurrentRegion.Rows.Count For i = 1 To rowCnt homeCell.Offset(i - 1, -1) = i Next i 2. Add this statement to set the print range: ActiveSheet.PageSetup.PrintArea = homeCell.CurrentRegion.Address --- Jay "sage" wrote: Hi, I'm a newbie with macros and need some help. I have a spreadsheet with data extracted by a macro and I've inserted Column A which is blank. I need to include something in the macro to auto fill the row numbers in column A to the end of the data. The number of rows is changeable. Also I need something that will set the print area each time the macro is run but the print area changes each time as well. I don't need to actually print the document though, just set the area ready to print. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFill Row Numbers & Print Area
Hi Sage -
This version adjusts for the header row. Keep the homeCell set to the top, left-most cell (B1). It's only purpose is to be reference point from which to sense your data range (with the CurrentRegion property). Also, once you run this you might notice that Excel shows the pagebreaks as dashed lines on the screen. These can be annoying sometimes, especially if you need to work on the sheet. The last statement in the procedure suppresses them. You can also turn them off from the user interface menu by clearing the [Page breaks] checkbox under |Tools|Options|{View Tab}|'Window Options' category. Sub sage() Set homeCell = Range("B1") '<<change to top, left-most data cell rowCnt = homeCell.CurrentRegion.Rows.Count For i = 1 To rowCnt - 1 homeCell.Offset(i, -1) = i Next i ActiveSheet.PageSetup.PrintArea = homeCell.CurrentRegion.Address ActiveSheet.DisplayPageBreaks = False '(turns pagebreak preview lines off) End Sub -- Jay "sage" wrote: Thanks Jay, that worked beautifully....only one small problem, I need to count the rows from row 2 (row 1 is headers). I changed the home cell to B2 but it counts one extra row at the bottom....how can I fix it? Thanks Sage "Jay" wrote: Hi sage - 1. Add these statements to your macro to number the rows: Set homeCell = Range("B1") '<<change to top, left-most data cell rowCnt = homeCell.CurrentRegion.Rows.Count For i = 1 To rowCnt homeCell.Offset(i - 1, -1) = i Next i 2. Add this statement to set the print range: ActiveSheet.PageSetup.PrintArea = homeCell.CurrentRegion.Address --- Jay "sage" wrote: Hi, I'm a newbie with macros and need some help. I have a spreadsheet with data extracted by a macro and I've inserted Column A which is blank. I need to include something in the macro to auto fill the row numbers in column A to the end of the data. The number of rows is changeable. Also I need something that will set the print area each time the macro is run but the print area changes each time as well. I don't need to actually print the document though, just set the area ready to print. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
set area in excel not giving me option to set print area? | Excel Discussion (Misc queries) | |||
File, print area, clear area, is not working | New Users to Excel | |||
How do you turn off a print area for a page? (no print area) | Excel Discussion (Misc queries) | |||
Q. Autofill question: Can I autofill alpha characters like I can numbers? | Excel Programming | |||
Setting Print Area, includes text, numbers, charts & graphs | Excel Programming |