How to define last row from a given starting point

[wrmcmahan12]

I'm trying to set print area based on how many rows in a range are not blank. I have tried the offset function to dynamically set a range, but Excel keeps converting the dynamic range to a static range. I'd like to have a button to press that says "print" and will set the proper print area in range $g$3:$J$100 ( if the last entry is in row 20 the range would be $G$3:$J$20). I understand how to do the last row vba function, but the issue is there is another data range below row 100 so I need to find the last row above row 101 for example. How could I adjust the vba to look for the last row above row 101 for instance?

 

[fhqwgads]

if there are no blank rows or columns within the region you want to print you could try use CurrentRegion

Sub currentregionselect()
ActiveCell.CurrentRegion.Select
End Sub

just sub in a cell in your desired print area for range instead of active cell and change select to whatever it is to set print area

 

[fhqwgads]

setting print area is different to how i thought. i think the code should be

Worksheets("Sheet1").PageSetup.PrintArea = Range("G3").CurrentRegion.Address

just change Sheet1 to whatever sheet it is

 

[wrmcmahan12]

That's for the reply. The only issue with current region is I only need to print to column I, but the data continues to column NX

 

[wrmcmahan12]

That's for the reply. The only issue with current region is I only need to print to column I, but the data continues to column NX

I meant "thanks" ?

 

[MARK858]

That's for the reply. The only issue with current region is I only need to print to column I, but the data continues to column NX

You can try

Worksheets("Sheet1").PageSetup.PrintArea = Intersect(Worksheets("Sheet1").Columns("G:I"), Worksheets("Sheet1").Range("G3").CurrentRegion).Address