VB and VBA Users Source Code: Excel VBA: Returning the next blank/empty cell in a range
[
Home
|
Contents
|
Search
|
Reply
| Previous | Next ]
VB/VBA Source Code
Excel VBA: Returning the next blank/empty cell in a range
By:
Andrew Baker
Email (spam proof):
Email the originator of this post
Date:
Wednesday, July 12, 2006
Hits:
4974
Category:
VBA (Visual Basic for Applications)
Article:
The code below will return the next blank cell given a reference range and a specified direction (eg. The next blank cell below this cell). 'Purpose : Returns the next blank in a given direction, at the specified offset. 'Inputs : rngReference The reference range. ' direction The direction to find the next blank cell in. ' [lOffset] The offset from the last used cell in the specified direction. 'Outputs : Returns the next blank in a given direction, at the specified offset. 'Author : Andrew Baker 'Date : 13/11/2000 10:14 'Notes : 'Revisions : Function GetNextBlankCell(rngReference As Excel.range, direction As XlDirection, Optional lOffset As Long = 1) Dim result As Excel.range On Error GoTo ErrFailed Set result = rngReference.End(direction) Select Case direction Case XlDirection.xlDown Set result = result.Offset(lOffset, 0) Case XlDirection.xlToLeft Set result = result.Offset(0, -lOffset) Case XlDirection.xlToRight Set result = result.Offset(0, lOffset) Case XlDirection.xlUp Set result = result.Offset(-lOffset, 0) End Select Set GetNextBlankCell = result Exit Function ErrFailed: Debug.Print "Error in GetNextBlankCell. " & Err.Description Set GetNextBlankCell = Nothing End Function 'Demonstration code Sub Test() Dim rngNextCell As Excel.range Set rngNextCell = GetNextBlankCell(range("A1"), xlToLeft) If (rngNextCell Is Nothing) = False Then Debug.Print rngNextCell.address End If End Sub
Terms and Conditions
Support this site
Download a trial version of the Excel Workbook Rebuilder
