VB Code - Find last row of data in a column

[Rahiem]

I am trying to setup a macro that formats my invoices sheets. The macro that I recorded is running okay but the print range is too large for the invoice and I don't want it set at one print range. I want excel to find the last row and column of data per set print range. In this case I have one invoice but problaly need two print ranges or ranges with variables setup to read last row of data from rows A1 to K1 End.xldown. The invoice row range varies from A1 to A150 but I want the print range to fit exactly the same size as my invoice. Here is what I have recorded please help me finx this. Thanks RG,

Range("B7").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.End(xlDown).Select
ActiveWindow.SmallScroll Down:=18
ActiveWindow.SmallScroll Down:=5
ActiveSheet.PageSetup.PrintArea = "A1:K100"
Range("B65536").End(xlUp).Select
ActiveWindow.View = xlPageBreakPreview
ActiveSheet.PageSetup.PrintArea = "A1:K100"
ActiveWindow.SmallScroll Down:=-55
End Sub

 

[VoG]

Hello and welcome to MrExcel.

Does this work as expected?

Sub PrStp()
With ActiveSheet
    .PageSetup.PrintArea = .UsedRange.Address
End With
End Sub

 

[Rahiem]

Okay I will try this one now. Thanks for your help.

 

[Rahiem]

It seems to give me an error in my script. I pasted your script at my End sub so that the VB would read from your end sub. My print range is still ending too far from the bottom of the invoice. My invoice's last row is A1:I53 and it's ending on my specific range per my code - ActiveSheet.PageSetup.PrintArea = "A1:k75". Should I take those out? Can you modify my code to work?

Sub Format_PDF()
'
' Format_PDF Macro
' Macro recorded 3/2/2009 by Rahiem Greenridge
'
' Keyboard Shortcut: Ctrl+Shift+P
Range("B7").Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
ActiveWindow.SmallScroll Down:=18
ActiveWindow.SmallScroll Down:=5
Range("B65536").End(xlUp).Select
ActiveSheet.PageSetup.PrintArea = "A1:k75"
ActiveWindow.SmallScroll Down:=-52
ActiveWindow.View = xlPageBreakPreview
ActiveSheet.PageSetup.PrintArea = "A1:k75"
ActiveWindow.SmallScroll Down:=-55
Sub PrStp()
With ActiveSheet
.PageSetup.PrintArea = .UsedRange.Address
End With
End Sub

 

[Rahiem]

I tired using relative reference and that didn't work either. Unless I am doing something wrong. Any suggestions about relative refference? I think you probably can come up with some code to set this up easier.

 

[VoG]

My code was intended to replace your code, not be added to it.

 

[Rahiem]

The code seems to be working. Thanks one more question. How can I make my code find the last row of data and insert "Totals" after the last line of data and on that same row move over 8 columns and insert =sum and add all above cell contents. I have the code setup right but if I use a bigger file the totals end up in the middle of the page instead of at the bottm where its belongs. Here is my recorded code:

Sub Convert_C()
'
' Convert_C Macro
' Macro recorded 3/3/2009 by RG
'
' Keyboard Shortcut: Ctrl+Shift+S
'
Range("H1").Select
Selection.End(xlDown).Select
Range("H739:H741").Select
Range("H741").Activate
Selection.ClearContents
Range("H739").Select
Selection.FormulaR1C1 = "=SUM(R[-737]C:R[-1]C)"
Selection.End(xlToLeft).Select
Selection.FormulaR1C1 = "Totals"
Rows("739:739").Select
Selection.Font.Bold = True
Range("B739").Select
Selection.End(xlToRight).Select
Range("H738").Select
Range(Selection, Selection.End(xlToLeft)).Select
Range("G738:H738").Select
Range("H738").Activate
Range(Selection, Selection.End(xlUp)).Select
Selection.NumberFormat = "0.00"
ActiveWindow.ScrollRow = 717
ActiveWindow.ScrollRow = 712
ActiveWindow.ScrollRow = 707
ActiveWindow.ScrollRow = 699
ActiveWindow.ScrollRow = 690
ActiveWindow.ScrollRow = 681
ActiveWindow.ScrollRow = 671
ActiveWindow.ScrollRow = 661
ActiveWindow.ScrollRow = 653
ActiveWindow.ScrollRow = 643
ActiveWindow.ScrollRow = 635
ActiveWindow.ScrollRow = 625
ActiveWindow.ScrollRow = 617
ActiveWindow.ScrollRow = 607
ActiveWindow.ScrollRow = 599
ActiveWindow.ScrollRow = 589
ActiveWindow.ScrollRow = 581
ActiveWindow.ScrollRow = 572
ActiveWindow.ScrollRow = 561
ActiveWindow.ScrollRow = 555
ActiveWindow.ScrollRow = 546
ActiveWindow.ScrollRow = 536
ActiveWindow.ScrollRow = 529
ActiveWindow.ScrollRow = 519
ActiveWindow.ScrollRow = 507
ActiveWindow.ScrollRow = 495
ActiveWindow.ScrollRow = 484
ActiveWindow.ScrollRow = 470
ActiveWindow.ScrollRow = 454
ActiveWindow.ScrollRow = 437
ActiveWindow.ScrollRow = 419
ActiveWindow.ScrollRow = 405
ActiveWindow.ScrollRow = 389
ActiveWindow.ScrollRow = 373
ActiveWindow.ScrollRow = 355
ActiveWindow.ScrollRow = 342
ActiveWindow.ScrollRow = 331
ActiveWindow.ScrollRow = 318
ActiveWindow.ScrollRow = 308
ActiveWindow.ScrollRow = 299
ActiveWindow.ScrollRow = 290
ActiveWindow.ScrollRow = 278
ActiveWindow.ScrollRow = 269
ActiveWindow.ScrollRow = 259
ActiveWindow.ScrollRow = 249
ActiveWindow.ScrollRow = 238
ActiveWindow.ScrollRow = 224
ActiveWindow.ScrollRow = 211
ActiveWindow.ScrollRow = 201
ActiveWindow.ScrollRow = 193
ActiveWindow.ScrollRow = 187
ActiveWindow.ScrollRow = 182
ActiveWindow.ScrollRow = 178
ActiveWindow.ScrollRow = 176
ActiveWindow.ScrollRow = 172
ActiveWindow.ScrollRow = 170
ActiveWindow.ScrollRow = 167
ActiveWindow.ScrollRow = 164
ActiveWindow.ScrollRow = 160
ActiveWindow.ScrollRow = 156
ActiveWindow.ScrollRow = 154
ActiveWindow.ScrollRow = 149
ActiveWindow.ScrollRow = 146
ActiveWindow.ScrollRow = 140
ActiveWindow.ScrollRow = 134
ActiveWindow.ScrollRow = 125
ActiveWindow.ScrollRow = 119
ActiveWindow.ScrollRow = 111
ActiveWindow.ScrollRow = 103
ActiveWindow.ScrollRow = 96
ActiveWindow.ScrollRow = 88
ActiveWindow.ScrollRow = 78
ActiveWindow.ScrollRow = 70
ActiveWindow.ScrollRow = 60
ActiveWindow.ScrollRow = 54
ActiveWindow.ScrollRow = 44
ActiveWindow.ScrollRow = 36
ActiveWindow.ScrollRow = 28
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 28
ActiveWindow.ScrollRow = 36
ActiveWindow.ScrollRow = 46
ActiveWindow.ScrollRow = 55
ActiveWindow.ScrollRow = 65
ActiveWindow.ScrollRow = 75
ActiveWindow.ScrollRow = 84
ActiveWindow.ScrollRow = 94
ActiveWindow.ScrollRow = 103
ActiveWindow.ScrollRow = 113
ActiveWindow.ScrollRow = 123
ActiveWindow.ScrollRow = 132
ActiveWindow.ScrollRow = 141
ActiveWindow.ScrollRow = 150
ActiveWindow.ScrollRow = 160
ActiveWindow.ScrollRow = 170
ActiveWindow.ScrollRow = 178
ActiveWindow.ScrollRow = 188
ActiveWindow.ScrollRow = 197
ActiveWindow.ScrollRow = 206
ActiveWindow.ScrollRow = 215
ActiveWindow.ScrollRow = 225
ActiveWindow.ScrollRow = 235
ActiveWindow.ScrollRow = 244
ActiveWindow.ScrollRow = 254
ActiveWindow.ScrollRow = 264
ActiveWindow.ScrollRow = 273
ActiveWindow.ScrollRow = 283
ActiveWindow.ScrollRow = 293
ActiveWindow.ScrollRow = 302
ActiveWindow.ScrollRow = 312
ActiveWindow.ScrollRow = 322
ActiveWindow.ScrollRow = 331
ActiveWindow.ScrollRow = 342
ActiveWindow.ScrollRow = 354
ActiveWindow.ScrollRow = 364
ActiveWindow.ScrollRow = 376
ActiveWindow.ScrollRow = 389
ActiveWindow.ScrollRow = 400
ActiveWindow.ScrollRow = 413
ActiveWindow.ScrollRow = 424
ActiveWindow.ScrollRow = 438
ActiveWindow.ScrollRow = 449
ActiveWindow.ScrollRow = 464
ActiveWindow.ScrollRow = 477
ActiveWindow.ScrollRow = 490
ActiveWindow.ScrollRow = 501
ActiveWindow.ScrollRow = 514
ActiveWindow.ScrollRow = 524
ActiveWindow.ScrollRow = 534
ActiveWindow.ScrollRow = 540
ActiveWindow.ScrollRow = 549
ActiveWindow.ScrollRow = 557
ActiveWindow.ScrollRow = 564
ActiveWindow.ScrollRow = 569
ActiveWindow.ScrollRow = 576
ActiveWindow.ScrollRow = 581
ActiveWindow.ScrollRow = 585
ActiveWindow.ScrollRow = 589
ActiveWindow.ScrollRow = 593
ActiveWindow.ScrollRow = 596
ActiveWindow.ScrollRow = 600
ActiveWindow.ScrollRow = 604
ActiveWindow.ScrollRow = 605
ActiveWindow.ScrollRow = 607
ActiveWindow.ScrollRow = 610
ActiveWindow.ScrollRow = 612
ActiveWindow.ScrollRow = 614
ActiveWindow.ScrollRow = 617
ActiveWindow.ScrollRow = 620
ActiveWindow.ScrollRow = 624
ActiveWindow.ScrollRow = 628
ActiveWindow.ScrollRow = 631
ActiveWindow.ScrollRow = 636
ActiveWindow.ScrollRow = 640
ActiveWindow.ScrollRow = 643
ActiveWindow.ScrollRow = 647
ActiveWindow.ScrollRow = 652
ActiveWindow.ScrollRow = 657
ActiveWindow.ScrollRow = 663
ActiveWindow.ScrollRow = 667
ActiveWindow.ScrollRow = 673
ActiveWindow.ScrollRow = 681
ActiveWindow.ScrollRow = 687
ActiveWindow.ScrollRow = 695
ActiveWindow.ScrollRow = 702
ActiveWindow.ScrollRow = 708
ActiveWindow.ScrollRow = 716
ActiveWindow.ScrollRow = 722
ActiveWindow.ScrollRow = 728
ActiveWindow.ScrollRow = 734
ActiveWindow.ScrollRow = 737
ActiveWindow.ScrollRow = 740
ActiveWindow.ScrollRow = 742
ActiveWindow.ScrollRow = 743
ActiveWindow.ScrollRow = 745
ActiveWindow.ScrollRow = 746
ActiveWindow.ScrollRow = 747
ActiveWindow.ScrollRow = 746
ActiveWindow.ScrollRow = 745
ActiveWindow.ScrollRow = 743
ActiveWindow.ScrollRow = 741
ActiveWindow.ScrollRow = 740
ActiveWindow.ScrollRow = 737
ActiveWindow.ScrollRow = 734
ActiveWindow.ScrollRow = 730
ActiveWindow.ScrollRow = 726
ActiveWindow.ScrollRow = 723
ActiveWindow.ScrollRow = 719
ActiveWindow.ScrollRow = 717
ActiveWindow.ScrollRow = 713
ActiveWindow.ScrollRow = 711
ActiveWindow.ScrollRow = 710
ActiveWindow.ScrollRow = 708
Range("B1:H738").Select
Range("H738").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Sort Key1:=Range("H2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

 

[VoG]

Try

Sub AddTotals()
Dim LR As Long
LR = Range("H" & Rows.Count).End(xlUp).Row
With Range("A" & LR + 1)
    .Value = "Totals:"
    .Font.Bold = True
End With
With Range("H" & LR + 1)
    .Formula = "=SUM(H2:H" & LR & ")"
    .Font.Bold = True
    .NumberFormat = "0.00"
End With
End Sub

 

[Rahiem]

Can I add this to my code or replace it?

 

[VoG]

It should replace your code.