function to return a "sub-range"

[fedude]

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

 

[Norman Jones]

Hi Ferdude,

Perhaps, trry something like:

Set myRng = Range("AP4").CurrentRegion

 

[Don Guillett]

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

 

[Dave Peterson]

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]

Won't this return the entire sheet?

Hi Ferdude,

Perhaps, trry something like:

Set myRng = Range("AP4").CurrentRegion

 

[Norman Jones]

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.

 

[fedude]

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!