Shift+Ctrl+End

  • Thread starter Thread starter Mick Southam
  • Start date Start date
M

Mick Southam

Can any one help with the code that is the equivalent to placing the cursor
in A1 then pressing Shift+Ctrl+End to highlight all cells with data in?
 
Ron de Bruin said:
Hi Mick

Try this
ActiveSheet.UsedRange.Select
Click to expand...
data in?

Not being particularly familiar with Visual Basic, I followed my usual
practice and recorded a temporary macro to get the code which
produced:-

Sub Macro1()
Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
End Sub

It's a bit less elegant but, apart from that, can anyone tell me if
the code has any serious disadvantages?

TIA,
 
Not being particularly familiar with Visual Basic, I followed my usual
practice and recorded a temporary macro to get the code which
produced:-

Sub Macro1()
Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
End Sub

It's a bit less elegant but, apart from that, can anyone tell me if
the code has any serious disadvantages?
Click to expand...


Do this experiment, make another macro using Ron's code.
Now fill A1:A20 with data, run both macros and the result will be the same,
now clear out A11:A20 and run both macros, note that your macro will still
select
A1:A20 while Ron's will select A1:A10


--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
 
Both are not good Peo<g>

Usedrange have the same problems as xlLastCell
format A11:A20 bold and press the delete button.
Usedrange also select A1:A20 now

You can use this

Sub Test()
Range(Cells(1, 1), Cells(LastRow(ActiveSheet), Lastcol(ActiveSheet))).Select
End Sub

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function

Function Lastcol(sh As Worksheet)
On Error Resume Next
Lastcol = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
End Function
 
Peo Sjoblom said:
Do this experiment, make another macro using Ron's code.
Now fill A1:A20 with data, run both macros and the result will be the same,
now clear out A11:A20 and run both macros, note that your macro will still
select
A1:A20 while Ron's will select A1:A10
Click to expand...


That's interesting! I note that CNTL/SHFT/END does the same thing as
my macro but that was to be expected.

Thanks,

Jim.
 
I use a macro and Function to make sure.

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

Function RangeToUse(anySheet As Worksheet) As Range
'this function returns the range from cells A1 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(.Cells(1, 1), .Cells(R, c))
End With
End Function

Gord Dibben Excel MVP
 
Back
Top