Excel VBA select range

0 votes

In the example below, I want to select the cells in Column B:BR of the current row and go to that place in the same workbook by double clicking on a cell. I'm employing the following code for this:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Application.Intersect(Target, Range("o7:o7")) Is Nothing Then
        Cancel = True
       Worksheets("INCOMING").Activate
       Range("r" & ActiveCell.Row & ":br" & ActiveCell.Row).Select  
    End If  
End Sub

but it keeps on giving me the "run-time error '1004' select method of range class failed.

There is always an entire row selected. Does anyone have any idea what I am doing wrong?

Feb 16, 2023 in Others by narikkadan
• 63,600 points
1,024 views

1 answer to this question.

0 votes

Please test the following event with adapted code. It presumes that the first double-clicked cell should be ActiveCell from your code ("O7"):

Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.address = "$O$7" Then
        Cancel = True
        With Worksheets("INCOMING")
            .Activate
            .Range("B" & Target.Row & ":BR" & Target.Row).Select
        End With
    End If
End Sub

Double-clicking on cell "O7", the seventh row of "INCOMING" sheet is selected between "B" and "BR" columns.

If you want to select the row of the ActiveCell of "INCOMING" sheet, you need to change Target.Row with ActiveCell.Row.

answered Feb 16, 2023 by Kithuzzz
• 38,000 points

Related Questions In Others

0 votes
1 answer

Excel-VBA - How to identify Target range (more than 1 cell) is deleted in a Worksheet_Change function?

You misunderstand the purpose of the function ...READ MORE

answered Sep 23, 2022 in Others by narikkadan
• 63,600 points
3,752 views
0 votes
1 answer

Excel VBA Worksheet Range storage location best practices

Solution Changing Worksheets' Code Name Why dim srcSheet as myWorksheet when ...READ MORE

answered Oct 21, 2022 in Others by narikkadan
• 63,600 points
449 views
0 votes
1 answer

Excel VBA remove blank rows from specific range

I have tried to avoid .select  Option Explicit Sub CombineData() ...READ MORE

answered Oct 23, 2022 in Others by narikkadan
• 63,600 points
1,136 views
0 votes
1 answer

how to select a statistical range in an excel

The lowest 5% and top 5% will ...READ MORE

answered Nov 4, 2022 in Others by narikkadan
• 63,600 points
725 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
1,246 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,692 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
954 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,690 points
1,083 views
0 votes
1 answer

Select data that meet criteria from a table, adding it to a combobox in userform VBA Excel

Fill Combo Box With Matches Sub GetSourceAcc() ...READ MORE

answered Mar 26, 2023 in Others by Kithuzzz
• 38,000 points
899 views
0 votes
1 answer

VBA to protect and unprotect in given range in sheet

It needs a minor tweak to become ...READ MORE

answered Nov 24, 2022 in Others by Kithuzzz
• 38,000 points
2,625 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP