'Active' Cells Issue When Running Macro

Hello. This is a similar thread to this one:
http://www.excelforum.com//showthread.php?t=613997 ... but a slightly different issue. I'll try to keep this simple:

I am trying to record and run a macro on an audit spreadsheet showing operator activity on a database.

The first issue which I have sorted is skinning down the report to only those rows that have record changes on the database (this was done by an ‘if’ statement and an auto filter command in the macro).

The second issue is to get the sheet to fit perfectly as 1 page wide by X (blank) pages tall. However I cannot do this. After the table is skinnied down, i.e from it's original 5000 rows to 3000 rows the printing macro (whether separate or the second part of the macro command) still includes all the (now) blank rows from where the whole sheet used to cover (down to 5000). Excel sees these deleted rows as still active. It doesn’t realise that these are no longer active cells. Is there any way around this, so I can get Excel to realise only the rows with data are active?

Btw, I have tried recording a printing parameters macro in every way possible, i.e. Selecting all cells and then trying to fit it with page set up, selecting cells by Ctl-down and ctl-across and many other methods) and it still recognises the old, now blanks cells as active. Any idea how to get round this?

This issue is more to do with Excel detecting / not detecting / mis-identifying active cells rather than printing issues.

If anyone is "interested" I have posted on another forum about this with no success here: http://discussions.virtualdr.com/sho...43#post1175943

Cheers!

I've had a similar problem before and this macro worked for me. Credit to Mr McRitchie.

Sub MakeLastCell()
  'David McRitchie,  http://www.mvps.org/dmcritchie/excel/lastcell.htm
  Dim x As Integer
  Dim str As String
  Dim xlong As Long, clong As Long, rlong As Long
  On Error GoTo 0
  x = MsgBox("Do you want the activecell to become " & _
      "the lastcell" & Chr(10) & Chr(10) & _
      "Press OK to Eliminate all cells beyond " _
      & ActiveCell.Address(0, 0) & Chr(10) & _
      "Press CANCEL to leave sheet as it is", _
      vbOKCancel + vbCritical + vbDefaultButton2)
  If x = vbCancel Then Exit Sub
  str = ActiveCell.Address
  Range(ActiveCell.Row + 1 & ":" & Cells.Rows.Count).Delete
  xlong = ActiveSheet.UsedRange.Rows.Count   'see J-Walkenbach tip 73
  xlong = ActiveSheet.UsedRange.Columns.Count 'might also help
  'use of filters can interfer with column elimination
  Range(Cells(1, ActiveCell.Column + 1), _
     Cells(Cells.Rows.Count, Cells.Columns.Count)).Delete
  Beep
  xlong = ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Columns.Count 'Tip73
  rlong = Cells.SpecialCells(xlLastCell).Row
  clong = Cells.SpecialCells(xlLastCell).Column
  If rlong <= ActiveCell.Row And clong <= ActiveCell.Column Then Exit Sub
 
  ActiveWorkbook.Save
  xlong = ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Columns.Count 'Tip73
  rlong = Cells.SpecialCells(xlLastCell).Row
  clong = Cells.SpecialCells(xlLastCell).Column
  If rlong <= ActiveCell.Row And clong <= ActiveCell.Column Then Exit Sub
  MsgBox "Sorry, Have failed to make " & str & " your last cell"
End Sub