Help with offset
 

Hi all,

I have the code below (kindly provided by a genius from this forum) that
determines the active print range for a sheet. It works perfectly, however,
I wish to expand this returned range by 1 row. I have experimented with
offset but so far have had no success. Any ideas would be welcomed!

With ActiveSheet
.PageSetup.PrintArea = ""
.PageSetup.PrintArea = ActiveSheet.Range("A1:Q" &
LastRowRange(ActiveSheet)).Address
End With

Function LastRowRange(sh As Worksheet)
'This function determines the active print range for a list and returns a
range object.

On Error Resume Next
LastRowRange = sh.Range("A:A").Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0

End Function

Help with offset
 

Add + 1 to the row reference in the function i.e:

Function LastRowRange(sh As Worksheet)

On Error Resume Next
LastRowRange = sh.Range("A:A").Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row + 1
On Error GoTo 0

End Function

Hope this helps
Rowan

lk wrote:
Hi all,

I have the code below (kindly provided by a genius from this forum) that
determines the active print range for a sheet. It works perfectly, however,
I wish to expand this returned range by 1 row. I have experimented with
offset but so far have had no success. Any ideas would be welcomed!

With ActiveSheet
.PageSetup.PrintArea = ""
.PageSetup.PrintArea = ActiveSheet.Range("A1:Q" &
LastRowRange(ActiveSheet)).Address
End With

Function LastRowRange(sh As Worksheet)
'This function determines the active print range for a list and returns a
range object.

On Error Resume Next
LastRowRange = sh.Range("A:A").Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0

End Function

Help with offset
 

I think you can use the .Resize method to extend the range. You will need a
dash of code to get the .Rows.Count of the range you are working with. Say
the range is Rng:

Set Rng = Rng.Resize(Rng.Rows.Count+1)

Also, be aware that LastRowRange FAILS if data in column(s) other than A
extend further down than the data in column A. Finding the very last cell or
row is very thorny. I know there are several good methods, the one below is
not very versatile. I generally use this:

LastRow = activesheet.usedrange.cells(1).row + _
activesheet.usedrange.rows.count

but it may not be perfect for you because rows which are just formatted
count as part of the UsedRange. I think you want to have a range with data
values only be chosen. So col A cuts it, go for it.


"lk" wrote in message
...
Hi all,

I have the code below (kindly provided by a genius from this forum) that
determines the active print range for a sheet. It works perfectly,
however, I wish to expand this returned range by 1 row. I have
experimented with offset but so far have had no success. Any ideas would
be welcomed!

With ActiveSheet
.PageSetup.PrintArea = ""
.PageSetup.PrintArea = ActiveSheet.Range("A1:Q" &
LastRowRange(ActiveSheet)).Address
End With

Function LastRowRange(sh As Worksheet)
'This function determines the active print range for a list and returns a
range object.

On Error Resume Next
LastRowRange = sh.Range("A:A").Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0

End Function

Help with offset
 

it can be made much simpler...

With ActiveSheet
.PageSetup.PrintArea = "A1:Q" & _
Application.WorksheetFunction.Match("*", .Range("A:A"), False)
End With

"lk" wrote:

Hi all,

I have the code below (kindly provided by a genius from this forum) that
determines the active print range for a sheet. It works perfectly, however,
I wish to expand this returned range by 1 row. I have experimented with
offset but so far have had no success. Any ideas would be welcomed!

With ActiveSheet
.PageSetup.PrintArea = ""
.PageSetup.PrintArea = ActiveSheet.Range("A1:Q" &
LastRowRange(ActiveSheet)).Address
End With

Function LastRowRange(sh As Worksheet)
'This function determines the active print range for a list and returns a
range object.

On Error Resume Next
LastRowRange = sh.Range("A:A").Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0

End Function

Help with offset
 

furthethermore, to offset by another row, just add the offset required...


With ActiveSheet
.PageSetup.PrintArea = "A1:Q" & _
Application.WorksheetFunction.Match("*", .Range("A:A"), False) + 1
End With


"lk" wrote:

Hi all,

I have the code below (kindly provided by a genius from this forum) that
determines the active print range for a sheet. It works perfectly, however,
I wish to expand this returned range by 1 row. I have experimented with
offset but so far have had no success. Any ideas would be welcomed!

With ActiveSheet
.PageSetup.PrintArea = ""
.PageSetup.PrintArea = ActiveSheet.Range("A1:Q" &
LastRowRange(ActiveSheet)).Address
End With

Function LastRowRange(sh As Worksheet)
'This function determines the active print range for a list and returns a
range object.

On Error Resume Next
LastRowRange = sh.Range("A:A").Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0

End Function

Help with offset
 

Hi Patrick,

Does:

Application.WorksheetFunction.Match("*", .Range("A:A"), False)

not return the row number of the first populated cell in column A, rather
than the last?


---
Regards,
Norman


"Patrick Molloy" wrote in message
...
furthethermore, to offset by another row, just add the offset required...


With ActiveSheet
.PageSetup.PrintArea = "A1:Q" & _
Application.WorksheetFunction.Match("*", .Range("A:A"), False) + 1
End With


"lk" wrote:

Hi all,

I have the code below (kindly provided by a genius from this forum) that
determines the active print range for a sheet. It works perfectly,
however,
I wish to expand this returned range by 1 row. I have experimented with
offset but so far have had no success. Any ideas would be welcomed!

With ActiveSheet
.PageSetup.PrintArea = ""
.PageSetup.PrintArea = ActiveSheet.Range("A1:Q" &
LastRowRange(ActiveSheet)).Address
End With

Function LastRowRange(sh As Worksheet)
'This function determines the active print range for a list and returns a
range object.

On Error Resume Next
LastRowRange = sh.Range("A:A").Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0

End Function

Help with offset
 

One mo

With ActiveSheet
.PageSetup.PrintArea = ""
.PageSetup.PrintArea = ActiveSheet.Range("A1:Q" & _
LastRowRange(ActiveSheet) + 1).Address
End With

lk wrote:

Hi all,

I have the code below (kindly provided by a genius from this forum) that
determines the active print range for a sheet. It works perfectly, however,
I wish to expand this returned range by 1 row. I have experimented with
offset but so far have had no success. Any ideas would be welcomed!

With ActiveSheet
.PageSetup.PrintArea = ""
.PageSetup.PrintArea = ActiveSheet.Range("A1:Q" &
LastRowRange(ActiveSheet)).Address
End With

Function LastRowRange(sh As Worksheet)
'This function determines the active print range for a list and returns a
range object.

On Error Resume Next
LastRowRange = sh.Range("A:A").Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0

End Function

--

Dave Peterson

Help with offset
 

it does what the original code does - finds the '*'

The last used row is a column is RANGE("A65000").End(XLup).Row

so if thats the method you want, use:

With ActiveSheet
.PageSetup.PrintArea = "A1:Q" & ( .Range("A65000").End(XLup).Row +1)
End With




"Norman Jones" wrote:

Hi Patrick,

Does:

Application.WorksheetFunction.Match("*", .Range("A:A"), False)

not return the row number of the first populated cell in column A, rather
than the last?


---
Regards,
Norman


"Patrick Molloy" wrote in message
...
furthethermore, to offset by another row, just add the offset required...


With ActiveSheet
.PageSetup.PrintArea = "A1:Q" & _
Application.WorksheetFunction.Match("*", .Range("A:A"), False) + 1
End With


"lk" wrote:

Hi all,

I have the code below (kindly provided by a genius from this forum) that
determines the active print range for a sheet. It works perfectly,
however,
I wish to expand this returned range by 1 row. I have experimented with
offset but so far have had no success. Any ideas would be welcomed!

With ActiveSheet
.PageSetup.PrintArea = ""
.PageSetup.PrintArea = ActiveSheet.Range("A1:Q" &
LastRowRange(ActiveSheet)).Address
End With

Function LastRowRange(sh As Worksheet)
'This function determines the active print range for a list and returns a
range object.

On Error Resume Next
LastRowRange = sh.Range("A:A").Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0

End Function

Help with offset
 

Thanks Rowan, Patrick, Dave and William,
Your responses are much appreciated!

I decided to go with Rowan's suggestion as it seemed a simple mod to my code
and it worked perfectly. I am adding the other suggestions to my Excel
knowledge base as I'm sure they will come in handy too.

Ciao
Lawrence

"Dave Peterson" wrote in message
...
One mo

With ActiveSheet
.PageSetup.PrintArea = ""
.PageSetup.PrintArea = ActiveSheet.Range("A1:Q" & _
LastRowRange(ActiveSheet) + 1).Address
End With

lk wrote:

Hi all,

I have the code below (kindly provided by a genius from this forum) that
determines the active print range for a sheet. It works perfectly,
however,
I wish to expand this returned range by 1 row. I have experimented with
offset but so far have had no success. Any ideas would be welcomed!

With ActiveSheet
.PageSetup.PrintArea = ""
.PageSetup.PrintArea = ActiveSheet.Range("A1:Q" &
LastRowRange(ActiveSheet)).Address
End With

Function LastRowRange(sh As Worksheet)
'This function determines the active print range for a list and returns a
range object.

On Error Resume Next
LastRowRange = sh.Range("A:A").Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0

End Function

--

Dave Peterson

Help with offset
 

Just a thought....

If that lastrowrange subroutine is used for different things, you may not want
to change it.

That's why I just added 1 to what that subroutine returned.



lk wrote:

Thanks Rowan, Patrick, Dave and William,
Your responses are much appreciated!

I decided to go with Rowan's suggestion as it seemed a simple mod to my code
and it worked perfectly. I am adding the other suggestions to my Excel
knowledge base as I'm sure they will come in handy too.

Ciao
Lawrence

"Dave Peterson" wrote in message
...
One mo

With ActiveSheet
.PageSetup.PrintArea = ""
.PageSetup.PrintArea = ActiveSheet.Range("A1:Q" & _
LastRowRange(ActiveSheet) + 1).Address
End With

lk wrote:

Hi all,

I have the code below (kindly provided by a genius from this forum) that
determines the active print range for a sheet. It works perfectly,
however,
I wish to expand this returned range by 1 row. I have experimented with
offset but so far have had no success. Any ideas would be welcomed!

With ActiveSheet
.PageSetup.PrintArea = ""
.PageSetup.PrintArea = ActiveSheet.Range("A1:Q" &
LastRowRange(ActiveSheet)).Address
End With

Function LastRowRange(sh As Worksheet)
'This function determines the active print range for a list and returns a
range object.

On Error Resume Next
LastRowRange = sh.Range("A:A").Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0

End Function

--

Dave Peterson

--

Dave Peterson