1

Background:
Having a Column A and a Column B with non standard numeration, I am trying to type that number to selected Sheets by pressing a button. IE:

Column A..........Column B
MySheet..................2

Problem:
I am not sure on how to get the address of the last printable cell in order to paste the number there

enter image description here


Please note, footer will not work since it is not following a sequence as such -it is user specified for her/him needs-, it could be footer 10 but, user wants it as 15.

Approach:
There is nothing -as you may see- in that cell (text and color in the image are just for shown purposes) that usedrange, last row or go to special could help me to address.
Question:
Is it possible to get something like?

Sheets("MySheet").Range(LastPrintable.Address).value = 2
Community
  • 1
  • 1
Sgdva
  • 2,800
  • 3
  • 17
  • 28
  • Can you make the area on the sheet a named range? – Ryan Wildry Nov 04 '16 at 22:52
  • @RyanWildry Nope, it's basically a copy/paste from another workbook and I handle that (it would be requesting the user to do so and it may be impractical to do so for each sheet copied, user would rather to numerate them manually if such condition is asked) in this Sub -or attempting to handle it-. – Sgdva Nov 04 '16 at 23:17
  • Are they setting a print area prior to printing? – Ryan Wildry Nov 04 '16 at 23:21
  • @RyanWildry Yeah and with the accepted solution it worked! – Sgdva Nov 05 '16 at 06:13

1 Answers1

1

I think this will work. It's a little piece-meal, but that should make it easier to follow. Feel free to consolidate it:

Dim ws As Worksheet
Dim printRange As Variant
Dim lastCell As Range

Set ws = Sheets("MySheet")
printRange = Split(ws.PageSetup.PrintArea, ":")
Set lastCell = Range(printRange(1))

"lastCell" now contains the last cell in the printable area.

lastCell.Value2 = 2

Since it's a Range Object, you can also get the row, column, etc from it as you see fit.

Hambone
  • 15,600
  • 8
  • 46
  • 69