AutoFill Row Numbers & Print Area

[Guest]

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.

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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