CODEWITHSUNDEEP
vbaexcelexcel-2013
Sgdva
Sgdva

Reputation: 2800

Last printable Cell Excel VBA

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

Upvotes: 1

Views: 80

Answers (1)

Hambone
Hambone

Reputation: 16397

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.

Upvotes: 1

Related Questions