VBA Code for "CTRL+HOME/END"?

[Lazarus416]

Is there a VBA code that I can use to mimic the ctrl+home/end function? When I record a macro manually and use those functions, it inserts code to select a specific cell rather than code to "goto the 1st cell" or "goto the last cell". Thanks in advance for any info.

Laz

 

[AlphaFrog]

I think Ctrl+Home always moves to cell A1 assuming it's visible. If A1 is not visible, then this moves to the 1st visible cell.
Cells.SpecialCells(xlCellTypeVisible)(1).Select

This is equivalent to Ctrl+End
Cells.SpecialCells(xlCellTypeLastCell).Select

 

[Lazarus416]

Appreciate it. I'll give that one a try.

Oh...one more related question, is there a VBA equivalent to ctrl+shift+end? As I sit here and think about it, that's really the one I need. For selecting all of the (variable sized) worksheet so that I can covert it to a table as part of the macro.

 

[AlphaFrog]

This selects contiguous data starting from A1
Range("A1").CurrentRegion.Select

Or this...
Range("A1", Cells.SpecialCells(xlCellTypeLastCell)).Select

 

[Lazarus416]

This selects contiguous data starting from A1
Range("A1").CurrentRegion.Select

Or this...
Range("A1", Cells.SpecialCells(xlCellTypeLastCell)).Select

Thanks Alpha, the first one worked great (once I figured out how to stick in the table conversion code). You just made my life MUCH easier.

For anyone who needs the full conversion code to convert a dynamic range into a table, here it is:

ActiveSheet.ListObjects.Add(xlSrcRange, Range("A1").CurrentRegion, , xlYes).Name = _
        "Table1"
    Range("Table1[#All]").Select
    ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleMedium16"

(The tablestyle can be whatever you want...)