ctrl-end in VBA

  • Thread starter Torstein S. Johnsen
  • Start date
T

Torstein S. Johnsen

I want to select a range from C3 to "last cell" in many different workbooks.
I know have to do this manually with SHIFT-CTRL-END but is there a simple
way to do this with VBÀ?

Torstein Johnsen
 
G

GJones

Torstein;

Use This

Range("C3").Select
Range(Selection, ActiveCell.SpecialCells
(xlLastCell)).Select

Thanks,

Greg
 
G

Gord Dibben

Torstein

The last cell may not be where you think it is.

Excel has a habit of including cells that once held data but were cleared.

Either of the two codes posted could give you an incorrect "last cell"
selection.

To reset the used range before running the code, see Debra Dalgleish's site

http://www.contextures.on.ca/xlfaqApp.html#Unused

Alternative..........

Add this UDF to your workbook........

Function RangeToUse(anySheet As Worksheet) As Range
'Bob Flanagan creation slightly modified by Gord Dibben
'this function returns the range from Activecell to cell which is the
'intersection of the last row with an entry and the last column with an entry.
'used with UsedRangePick macro.....REAL USED RANGE!!
Dim i As Integer, c As Integer, R As Integer

With anySheet.UsedRange
i = .Cells(.Cells.Count).Column + 1
For c = i To 1 Step -1
If Application.CountA(anySheet.Columns(c)) > 0 _
Then Exit For
Next
i = .Cells(.Cells.Count).Row + 1
For R = i To 1 Step -1
If Application.CountA(anySheet.Rows(R)) > 0 Then _
Exit For
Next
End With

With anySheet
Set RangeToUse = .Range(ActiveCell, .Cells(R, c))
'note activecell could be hard-coded to a specific cell reference
End With
End Function

Then run this macro.

Sub UsedRangePick()
Dim tempRange As Range
Set tempRange = RangeToUse(ActiveSheet)
tempRange.Select
End Sub

Gord Dibben Excel MVP
 
Top