[dba-Tech] Assign current selection to Range object

Hi Susan:

Here is a piece of code I designed to delete a range within code.

<code>
Public Function MoveRange()
    ' Create a range from the current row and first column
    ' and extending down to the last row of data. This function
    ' under program control, deletes the current row
    ' by copying all the data below the current position and
    ' to the last row of data and pasting it, overtop starting
    ' at the current position.
    ' This routine manges the problem of deleting an entite row
    ' but limiting the effect to a range of columns.
    ' Create by Jim Lawrence 15 July 2006.
    
    Dim lngRowPosition As Long
    Dim lngRowCount As Long
    Dim strCell As String
        
    ' Get currenty row position
    lngRowPosition = Application.ActiveCell.Row
    lngRowCount = 0
    
    ' Establish the top left hand corner of the range
    strCell = "a" & lngRowPosition
    
    ' Stop screen display
    Application.ScreenUpdating = False
    
    ' Get the row count of the range from the current row
    ' to the last row with data.
    lngRowCount = GetRecordPosition()
 
    ' Set the start of the Range (top left-hand corner)
    Range(strCell).Select
    
    ' Select the data within the range coordinates, given the offset
    ' and copy all the data. The range selection is actaully one row below
    ' the current row and one row below the last row of data.
    Selection.CurrentRegion.Select
    Selection.Offset(lngRowPosition, 0).Resize(lngRowCount - lngRowPosition
+ 2).Select
    Selection.Copy
    
    ' Re-set the range start position
    Range(strCell).Select
    
    ' Now paste the data and clear the buffer
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
    ' Turn on the screen display.
    Application.ScreenUpdating = True

    ' Set the current position to the initial start position
    Range(strCell).Select
    
End Function

Public Function GetRecordPosition() As Integer
    ' Find the last row with data below the start position
    Dim intCounter As Integer
    
    ' Set current row
    Range("a2").Select
    
    intCounter = 0
    
    Do
        If IsEmpty(ActiveCell) = False Then
            intCounter = intCounter + 1
            ActiveCell.Offset(1, 0).Select
        End If
    Loop Until IsEmpty(ActiveCell) = True
    
    ' Set back to current row
    Range("a2").Select
    
    GetRecordPosition = intCounter
    
End Function
</code>

HTH
Jim

-----Original Message-----
From: dba-tech-bounces at databaseadvisors.com
[mailto:dba-tech-bounces at databaseadvisors.com] On Behalf Of Susan Harkins
Sent: Tuesday, March 18, 2008 9:29 AM
To: DBA Tech List
Subject: [dba-Tech] Assign current selection to Range object

I've run into this before and had trouble -- what is the simplest way to
define a Range object using the current range selection in a sheet? 

For instance, if I select cells A1:B4, I want VBA to create a Range object
that equals A1:B4. 

This shouldn't totally stump me, but I just can't get it and can't find
anything in Help -- thought I was on the right track with CurrentRegion, but
couldn't get it right. 

Susan H. 
_______________________________________________
dba-Tech mailing list
dba-Tech at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-tech
Website: http://www.databaseadvisors.com