Please need help with last row with data

Drow = Cells(Rows.Count, 4).End(xlUp).Address

Ivan

Sorry Ivan, do I have to do I need to set something or dim?

this is the code that I have and it doesn't work to get the last row address.

Sub lastcell1()

Drow = Cells(Rows.Count, 4).End(xlUp).Address

End Sub

thanks

Not a direct answer but possibly helpful

Formula wise there is a way (I'm still a beginner VBA user- but maybe you can apply this somehow).

The Ideal case to return the address of the last non-blank cell in column D:

=OFFSET(D1,COUNTA(D:D)-1,0)

*assumes no blank cells Anywhere starting at D1.

Non-Ideal Case (some blanks in the range).

Checking rows 1:500 (this is an array formula, the calculation can take a while if you want to search through more rows).

{=INDIRECT(ADDRESS(MAX((ROW(1:500)*(A1:A500<>""))),COLUMN(A:A)))}

*in a cell you'd hit Ctrl+Shift+Enter to get the brackets. These formulas came from a couple examples in John Walkenbach's Excel 2000 Formulas book.

Hopefully there's an easier approach in VBA, but if not maybe this formulas can help.

Good-Luck
Adam S.

Run into another problem please helppp

Ok, I made it return the address of the last row with data. But now when tring to use that referece to set up the printing area ofcourse it doesn't work. The last row with data is D23 and I want the macro to select and set up as print area the range from A1:D23. Here is my humble code:

Sub testsetprintingarea()

Dim myrange As range

myrange=Cells(Rows.Count, 4).End(xlUp).Address

ActiveSheet.PageSetup.PrintArea = "$A$1:myrange"

End Sub

Drow = Cells(Rows.Count, 4).End(xlUp).Address Ivan : How can I get programmaticaly the address of the last row (not rows or cell) that have info from column D. : Actually what I need is a macro that will select the printing area in a worksheet and set it up as printing area; that will be starting from the right down corner, D34 to A1; the problem is that the number of rows changes so if I use reference to the cell D34 it will not work when I have more or less rows. So I need to make referece to the address of the last row with info in column D. Thank you so much for your help. :

Re: Run into another problem please helppp

Your Printarea is expecting a String expression
so change myrange to String and NOT Range.
Also range address should be in the format
"A1:D23" so => "A1:" & myrange will give you
this format.

ie.

Sub testsetprintingarea()

Dim myrange As String

myrange = Cells(Rows.Count, 4).End(xlUp).Address

ActiveSheet.PageSetup.PrintArea = "$A$1:" & myrange

End Sub

Ok, I made it return the address of the last row with data. But now when tring to use that referece to set up the printing area ofcourse it doesn't work. The last row with data is D23 and I want the macro to select and set up as print area the range from A1:D23. Here is my humble code: Sub testsetprintingarea() Dim myrange As range myrange=Cells(Rows.Count, 4).End(xlUp).Address ActiveSheet.PageSetup.PrintArea = "$A$1:myrange" End Sub

: : Drow = Cells(Rows.Count, 4).End(xlUp).Address : : Ivan