Selecting 'Ctrl+Home' via VBA

[GeneralShamu]

Any ideas? I don't want to select A1 by default given there may subsections within these sheets.

 

[Richard Schollar]

Perhaps something like:

Activecell.CurrentRegion.Range("A1").Select

 

[Joe4]

That Range("A1") sometimes throws code newbies for a loop, because at first glance it appears it might be selecting cell A1, when it is really selecting the first cell in the current region.

Sometimes, to avoid the confusion, I will write it like this:

    ActiveCell.CurrentRegion.Cells(1, 1).Select

It does the exact same thing as Richard's code, just represented a little differently.

 

[GeneralShamu]

Thank you guys; I appreciate it!

 

[GeneralShamu]

That Range("A1") sometimes throws code newbies for a loop, because at first glance it appears it might be selecting cell A1, when it is really selecting the first cell in the current region.

Sometimes, to avoid the confusion, I will write it like this:

    ActiveCell.CurrentRegion.Cells(1, 1).Select

It does the exact same thing as Richard's code, just represented a little differently.

So it seems the macro still crashes even with this.

What is happening is that there are plots/graphs on these worksheets and if those are selected then it doesn't actually go to cell A1...

 

[Richard Schollar]

So there are embedded charts? If a chart is selected, what do you want to happen?

 

[Rick Rothstein]

Sometimes, to avoid the confusion, I will write it like this:

    ActiveCell.CurrentRegion.Cells(1, 1).Select

It does the exact same thing as Richard's code, just represented a little differently.

When going for the first cell (you can't depend of this method for other cells in the range unless you know it's a single column or row), I simply treat the range as a one-dimensional array...

ActiveCell.CurrentRegion(1).Select

 

[GeneralShamu]

So there are embedded charts? If a chart is selected, what do you want to happen?

I want to select cell A1/'Ctrl + Home'

 

[Richard Schollar]

Perhaps:

If TypeName(Selection)="Range" Then
  ActiveCell.CurrentRegion.Range("A1").Select
Else
  Activesheet.Range("A1").Select
End if

It's not foolproof but should cater for most situations.