CODEWITHSUNDEEP
vbaexcel
Sam M
Sam M

Reputation: 47

Select a range of cells starting from the Active Cell

I need help with the code below. What I am trying to do is, starting from the active cell (which could be any cell), select all cells to the right (=first column) + all the cells to the left (=last column) + all the cells above until the highlighted row + all the cell below until the highlighted row. Please see the attached data Sample Data

As an example, in the sample data, if the active cell is G6, then the code would select the entire range from A2 to J7. Similarly, if the active cell is F12, the code would select the entire range from A11 to J13. 

Sub sel()
Dim LastCol As Long
With ActiveSheet
    LastCol = .Range("A1").SpecialCells(xlCellTypeLastCell).Column
Dim FirstCol As Long
With ActiveSheet
LastVrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Range(Cells(WorksheetFunction.Max(1, Selection.Row, LastVrow), _
  WorksheetFunction.Max(1, Selection.Column, LastCol)), _
  Cells(WorksheetFunction.Min(Selection.Worksheet.Rows.Count, _
  Selection.Row), _
  WorksheetFunction.Min(Selection.Worksheet.Columns.Count, _
  Selection.Column, FirstCol))).Select
 End With
End With
 End With
End Sub

Upvotes: 1

Views: 4083

Answers (3)

Kellsens
Kellsens

Reputation: 312

considering your template, maybe this could help:

sub sel()
    dim selectRowS as integer
    dim selectRowE as integer    

    with Selection    

        selectRowS = .row
        selectRowE = .row

        If Cells(.row + 1, 1) <> ""
            selectRowE = .end(xlEnd).row
        End If 
        If Cells(.row - 1, 1) <> ""
            if .row - 1 = 1 then
                selectRowS = 2
            Else
                selectRowS = .end(xlUp).row
            End if
        End If 

        Range(Cells(selectRowS,1),Cells(selectRowE,10)).select

    End With
End Sub

P.S. Sorry my english

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152660

This will do what you want:

Sub foo2()
Dim rng As Range
Dim st As Long
Dim fin As Long
With Selection
    If Cells(.Row - 1, 1) <> "" Then
        st = Cells(.Row, 1).End(xlUp).Row
        If st = 1 Then st = 2
    Else
        st = .Row
    End If
    If Cells(.Row + 1, 1) <> "" Then
        fin = Cells(.Row, 1).End(xlDown).Row
    Else
        fin = .Row
    End If
    Set rng = Range("A" & st & ":J" & fin)

End With
rng.Select
End Sub

Upvotes: 2

Scott Ridings
Scott Ridings

Reputation: 844

Maybe you could try this. Wherever the activecell cell is, first, move it to the first position and then select the entire block of data.

Sub Sel()

    Selection.End(xlToLeft).Select 
    Selection.End(xlUp).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select

End Sub

Upvotes: 0

Related Questions