Jumping to the Cells in a Selected Range - The Ctrl+. shortcut - VBA code?

norts55

Board Regular
Joined
Jul 27, 2012
Messages
144
Hello,
I came across a keyboard shortcut that allow me to jump the corner cells of a selected range.

The keystrokes are Ctrl+.

If I have a rectangular range of cells selected and I hit Ctrl+. the actual selected cell will jump to the corners of my range.

My question is, Is there VBA code that does this same thing? I need code that does this keystroke shortcut (Ctrl+.) three times.

Hopefully this make sense to someone. I am not sure if I can post a link to another website so I will just give this - If you search "How To Jump to the Last Cell in a Selected Range", this will show you the keyboard shortcut and maybe you will better understand what I am looking for.

Thank you in advance.
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,973
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Ctrl + . 3 times for me is the equivalent of
VBA Code:
Selection.Cells(Selection.Rows.Count, 1).Activate
which isn't to me the last cell in the selected range which is
VBA Code:
Selection.Cells(Selection.Cells.Count).Activate
If your activecell started in the top left corner.

Edit: changed .Select to .Activate as more appropriate.
 
Last edited:

norts55

Board Regular
Joined
Jul 27, 2012
Messages
144
So what you gave me works very well and does exactly what I was asking for. However, I am trying to put it in a current routine of mine to replace a message box and eliminate a mouse click, that can lead to a mistake, if the click is not accurate.

The code for the macro is quite long and I was able to make it by recording macros and piecing together answers to questions of mine on this forum. Again, I am by no means a programmer but I can piece together code. I have been struggling with this for a day now. So, I figure it is time to ask for help.

The part of the macro I cannot figure out is...
VBA Code:
Set myRange = Application.InputBox(Prompt:="In Column A, Select First Cell Below Last Footer", Title:="Format Titles", Type:=8)

What I have tried is...
Code:
Set myRange = Selection.Cells(Selection.Rows.Count, 1).Activate.ActiveCell.Offset(1, -4).Select

Here is the entire code....
Code:
Sub a_1_Combine_Takeoff_Items()
'
' a_1_Combine_Takeoff_Items Macro
'

'
'
        Response = MsgBox("Run Combine Takeoff Items Macro? -- If Yes, be sure of your selection", vbYesNo)
If Response = vbNo Then Exit Sub
 
Set selectRng = Selection
 
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlDouble
        .Color = -13312
        .TintAndShade = 0
        .Weight = xlThick
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlDouble
        .Color = -13312
        .TintAndShade = 0
        .Weight = xlThick
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlDouble
        .Color = -13312
        .TintAndShade = 0
        .Weight = xlThick
    End With


    Dim rng As Range
    Dim lFirst As Long, lLast As Long
    

    Set rng = Selection
    
    If Not rng Is Nothing Then
        lFirst = rng.Row
        lLast = rng.Rows.Count + lFirst - 1
        Range("z_1a_Sub_Cost").Formula = "=SUMIF('Takeoff'!$G$" & lFirst & ":$G$" & lLast _
            & ",""*Subcontractor*"",'Takeoff'!$O$" & lFirst & ":$O$" & lLast & ")"
    End If
    If Not rng Is Nothing Then
        lFirst = rng.Row
        lLast = rng.Rows.Count + lFirst - 1
        Range("z_1_Incidental_Cost").Formula = "=SUMIF('Takeoff'!$E$" & lFirst & ":$E$" & lLast _
            & ",""Incidentals Cost"",'Takeoff'!$F$" & lFirst & ":$F$" & lLast & ")"
    End If
    If Not rng Is Nothing Then
        lFirst = rng.Row
        lLast = rng.Rows.Count + lFirst - 1
        Range("z_2_Mat._Cost").Formula = "=SUMIF('Takeoff'!$G$" & lFirst & ":$G$" & lLast _
            & ",""Mat. Cost"",'Takeoff'!$H$" & lFirst & ":$H$" & lLast & ")"
    End If
    If Not rng Is Nothing Then
        lFirst = rng.Row
        lLast = rng.Rows.Count + lFirst - 1
        Range("z_3_Tax").Formula = "=SUMIF('Takeoff'!$I$" & lFirst & ":$I$" & lLast _
            & ",""Tax"",'Takeoff'!$J$" & lFirst & ":$J$" & lLast & ")"
    End If
    If Not rng Is Nothing Then
        lFirst = rng.Row
        lLast = rng.Rows.Count + lFirst - 1
        Range("z_4_Labor_Cost").Formula = "=SUMIF('Takeoff'!$K$" & lFirst & ":$K$" & lLast _
            & ",""Labor Cost"",'Takeoff'!$L$" & lFirst & ":$L$" & lLast & ")"
    End If
    If Not rng Is Nothing Then
        lFirst = rng.Row
        lLast = rng.Rows.Count + lFirst - 1
        Range("z_5_Equip_Cost").Formula = "=SUMIF('Takeoff'!$M$" & lFirst & ":$M$" & lLast _
            & ",""Equip Cost"",'Takeoff'!$N$" & lFirst & ":$N$" & lLast & ")"
    End If
    If Not rng Is Nothing Then
        lFirst = rng.Row
        lLast = rng.Rows.Count + lFirst - 1
        Range("z_6_Days").Formula = "=SUMIF('Takeoff'!$M$" & lFirst & ":$M$" & lLast _
            & ",""Equip Cost"",'Takeoff'!$O$" & lFirst & ":$O$" & lLast & ")"
    End If
    If Not rng Is Nothing Then
        lFirst = rng.Row
        lLast = rng.Rows.Count + lFirst - 1
        Range("z_7_Total_Cost").Formula = "=SUMIF('Takeoff'!$J$" & lFirst & ":$J$" & lLast _
            & ",""Total Cost"",'Takeoff'!$K$" & lFirst & ":$K$" & lLast & ")"
    End If
    

Dim myRange As Range
Dim CopyRange As Range

Set CopyRange = Range("_0_a_a_Combo_Box_Footer")



On Error Resume Next
Set myRange = Selection.Cells(Selection.Rows.Count, 1).Activate.ActiveCell.Offset(1, -4).Select

If myRange Is Nothing Then
    MsgBox "No selection made", vbCritical, "Input required"
    Exit Sub
End If

CopyRange.Copy

myRange.Insert Shift:=xlDown


    Dim rngFound As Range, rngAll As Range
    Dim strFirst As String, lColor As Variant, arrColors As Variant
        
    arrColors = Array(RGB(193, 1, 0), RGB(73, 69, 41), RGB(13, 13, 13), RGB(38, 38, 38), RGB(64, 64, 64), RGB(22, 54, 92), RGB(2, 2, 2), RGB(1, 2, 2), RGB(1, 2, 1), RGB(30, 0, 0), RGB(40, 1, 1), RGB(40, 0, 0), RGB(20, 33, 33))
    With Selection
        For Each lColor In arrColors
            With Application.FindFormat
                .Clear
                .Font.Color = lColor        'Search for font color
            End With
            On Error Resume Next
            Set rngFound = .Find("", .Cells(.Cells.Count), SearchFormat:=True)
            On Error GoTo 0
            If Not rngFound Is Nothing Then
                strFirst = rngFound.Address
                Do
                    If rngAll Is Nothing Then
                        Set rngAll = rngFound
                    Else
                        Set rngAll = Union(rngAll, rngFound)
                    End If
                    Set rngFound = .Find("", rngFound, SearchFormat:=True)
                Loop While rngFound.Address <> strFirst
            End If
        Next lColor
    End With
    
    If Not rngAll Is Nothing Then
        rngAll.Select
        Set rngAll = Nothing
        Set rngFound = Nothing
        strFirst = vbNullString
    Else
        MsgBox "No cells found with any font color "
    End If
    Application.FindFormat.Clear

    With Selection.Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    
    selectRng.Select
    Application.Run "'" & ActiveWorkbook.Name & "'" & "!z_Link_Line_Number"
    
    End With
    
    Application.Run "'" & ActiveWorkbook.Name & "'" & "!z_Make_Combo_Footer_Fomulas_Relative"
    
        Response = MsgBox("Combo Box Complete - Be Sure to Modify: Item #, Cost Cost, Bid Item, Unit, Unit Qty and Sub Cost", vbOK)
If Response = vbOK Then Exit Sub

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,127,217
Messages
5,623,453
Members
415,970
Latest member
ZorroOP

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top