function to return a "sub-range"
 

I have a range AP4:AQ63 that has data in the top few cells in the range. The
remaining cells are empty (I use delete to clear them).

I need a routine to read this range and return a smaller range that only has
non-blank cells in it. For example, if there are only 15 rows filled in in
the range, the function would return AP4:AQ19.

I have a loop that goes through the range, I just need some help
constructing the range to return. Here is what I have so far (thanks to
snippets I've gotten from this forum):

Function GetSubRange() As Range

Dim r As Range
Set r = Worksheets("Subs").Range("AP4")

Do While Not r = ""
Set r = r.Offset(1, 0)
Loop

GetSubRange = ???

End Function

function to return a "sub-range"
 

Hi Ferdude,

Perhaps, trry something like:

Set myRng = Range("AP4").CurrentRegion


---
Regards.
Norman


"fedude" wrote in message
...
I have a range AP4:AQ63 that has data in the top few cells in the range.
The
remaining cells are empty (I use delete to clear them).

I need a routine to read this range and return a smaller range that only
has
non-blank cells in it. For example, if there are only 15 rows filled in
in
the range, the function would return AP4:AQ19.

I have a loop that goes through the range, I just need some help
constructing the range to return. Here is what I have so far (thanks to
snippets I've gotten from this forum):

Function GetSubRange() As Range

Dim r As Range
Set r = Worksheets("Subs").Range("AP4")

Do While Not r = ""
Set r = r.Offset(1, 0)
Loop

GetSubRange = ???

End Function

function to return a "sub-range"
 

See if this idea helps
Sub lastcell()
For i = 1 To Sheets("yoursheet").UsedRange.Rows.Count
doyourthing
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"fedude" wrote in message
...
I have a range AP4:AQ63 that has data in the top few cells in the range.
The
remaining cells are empty (I use delete to clear them).

I need a routine to read this range and return a smaller range that only
has
non-blank cells in it. For example, if there are only 15 rows filled in
in
the range, the function would return AP4:AQ19.

I have a loop that goes through the range, I just need some help
constructing the range to return. Here is what I have so far (thanks to
snippets I've gotten from this forum):

Function GetSubRange() As Range

Dim r As Range
Set r = Worksheets("Subs").Range("AP4")

Do While Not r = ""
Set r = r.Offset(1, 0)
Loop

GetSubRange = ???

End Function

function to return a "sub-range"
 

Another one:

Option Explicit
Function GetSubRange(BeginningRng As Range) As Range

Dim TestRng As Range

'think negative!
Set GetSubRange = Nothing

If IsEmpty(BeginningRng.Cells(1).Value) Then
Exit Function
End If

'only one area!
Set BeginningRng = BeginningRng.Areas(1)

Set TestRng = Intersect(BeginningRng.Cells(1).CurrentRegion, BeginningRng)

Set TestRng = TestRng.Resize(, BeginningRng.Columns.Count)

Set GetSubRange = TestRng

End Function
Sub testme()

Dim myRng As Range
Dim mySubRng As Range

Set myRng = Worksheets("Subs").Range("ap4:aq63")

Set mySubRng = GetSubRange(myRng)

If mySubRng Is Nothing Then
MsgBox "No subrange"
Else
MsgBox mySubRng.Address
End If

End Sub


fedude wrote:

I have a range AP4:AQ63 that has data in the top few cells in the range. The
remaining cells are empty (I use delete to clear them).

I need a routine to read this range and return a smaller range that only has
non-blank cells in it. For example, if there are only 15 rows filled in in
the range, the function would return AP4:AQ19.

I have a loop that goes through the range, I just need some help
constructing the range to return. Here is what I have so far (thanks to
snippets I've gotten from this forum):

Function GetSubRange() As Range

Dim r As Range
Set r = Worksheets("Subs").Range("AP4")

Do While Not r = ""
Set r = r.Offset(1, 0)
Loop

GetSubRange = ???

End Function

--

Dave Peterson

function to return a "sub-range"
 

Won't this return the entire sheet?

"Norman Jones" wrote:


Hi Ferdude,

Perhaps, trry something like:

Set myRng = Range("AP4").CurrentRegion


---
Regards.
Norman


"fedude" wrote in message
...
I have a range AP4:AQ63 that has data in the top few cells in the range.
The
remaining cells are empty (I use delete to clear them).

I need a routine to read this range and return a smaller range that only
has
non-blank cells in it. For example, if there are only 15 rows filled in
in
the range, the function would return AP4:AQ19.

I have a loop that goes through the range, I just need some help
constructing the range to return. Here is what I have so far (thanks to
snippets I've gotten from this forum):

Function GetSubRange() As Range

Dim r As Range
Set r = Worksheets("Subs").Range("AP4")

Do While Not r = ""
Set r = r.Offset(1, 0)
Loop

GetSubRange = ???

End Function

function to return a "sub-range"
 

Hi Fedude,

If the columns on either side of the range
of interest, i.e. columns AO and AR, are empty,

Range("AP4").CurrentRegion

will return the range you require.

If this is not appropriate to your situation,
you have other responses.



---
Regards.
Norman


"fedude" wrote in message
...
Won't this return the entire sheet?

"Norman Jones" wrote:


Hi Ferdude,

Perhaps, trry something like:

Set myRng = Range("AP4").CurrentRegion


---
Regards.
Norman


"fedude" wrote in message
...
I have a range AP4:AQ63 that has data in the top few cells in the range.
The
remaining cells are empty (I use delete to clear them).

I need a routine to read this range and return a smaller range that
only
has
non-blank cells in it. For example, if there are only 15 rows filled
in
in
the range, the function would return AP4:AQ19.

I have a loop that goes through the range, I just need some help
constructing the range to return. Here is what I have so far (thanks
to
snippets I've gotten from this forum):

Function GetSubRange() As Range

Dim r As Range
Set r = Worksheets("Subs").Range("AP4")

Do While Not r = ""
Set r = r.Offset(1, 0)
Loop

GetSubRange = ???

End Function

function to return a "sub-range"
 

Dave,

This solution was perfect. Thank you. Not sure I totally understand all
the methods you employed, but it works and I can figure it out by stepping
through it.

The only gotcha (as mentioned by Norman Jones) in a previous post is that
the adjoining columns must be empty or it does not work. Took me a while to
catch this because initially, the adjoining columns were not blank.
Important safety tip....

Thanks!

"Dave Peterson" wrote:

Another one:

Option Explicit
Function GetSubRange(BeginningRng As Range) As Range

Dim TestRng As Range

'think negative!
Set GetSubRange = Nothing

If IsEmpty(BeginningRng.Cells(1).Value) Then
Exit Function
End If

'only one area!
Set BeginningRng = BeginningRng.Areas(1)

Set TestRng = Intersect(BeginningRng.Cells(1).CurrentRegion, BeginningRng)

Set TestRng = TestRng.Resize(, BeginningRng.Columns.Count)

Set GetSubRange = TestRng

End Function
Sub testme()

Dim myRng As Range
Dim mySubRng As Range

Set myRng = Worksheets("Subs").Range("ap4:aq63")

Set mySubRng = GetSubRange(myRng)

If mySubRng Is Nothing Then
MsgBox "No subrange"
Else
MsgBox mySubRng.Address
End If

End Sub


fedude wrote:

I have a range AP4:AQ63 that has data in the top few cells in the range. The
remaining cells are empty (I use delete to clear them).

I need a routine to read this range and return a smaller range that only has
non-blank cells in it. For example, if there are only 15 rows filled in in
the range, the function would return AP4:AQ19.

I have a loop that goes through the range, I just need some help
constructing the range to return. Here is what I have so far (thanks to
snippets I've gotten from this forum):

Function GetSubRange() As Range

Dim r As Range
Set r = Worksheets("Subs").Range("AP4")

Do While Not r = ""
Set r = r.Offset(1, 0)
Loop

GetSubRange = ???

End Function

--

Dave Peterson