Forum Discussion

WallisMcMath's avatar
WallisMcMath
Copper Contributor
May 05, 2020

Selecting an Excel Range in VBA

I have code in a Command Button which is in Worksheet "Weekly". The code in the command button is trying to select a range in another worksheet in the same workbook. The other worksheet is named "G...
Macros and VBA
Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
May 06, 2020

WallisMcMath 

If you just want to select B2:L34 in the Groups sheet as you describe, the following code lines will work.

 

DateR = 2
NameC = 2
LastNameR = 34
GFriIndexC = 12

Sheets("Groups").Range(Cells(DateR, NameC), Cells(LastNameR, GFriIndexC)).Select

 

 What you want to do with it, once selected (copy, clear, delete, change format etc.), is another issue. 

  • WallisMcMath's avatar
    WallisMcMath
    Copper Contributor
    May 06, 2020

    Riny_van_Eekelen 

    Thanks much for the response, but what you listed is what I tried.

    The code is in a CommandButton click event which is in the worksheet "Weekly" and the area to be selected is in Worksheet "Groups"

     

    The following  code Works

    Worksheets("Groups").Activate

    Sheets("Groups").Range("B2:L34").Select

     

    The following does not work

    Worksheets("Groups").Activate

    Sheets("Groups").Range(Cells(2,4), Cells(34,12)).Select   '(error occurs on this line of code)

    (I removed the variable names and replaced with numbers)

    The error message is Application-defined or object-defined error

     

      • WallisMcMath's avatar
        WallisMcMath
        Copper Contributor
        May 06, 2020

        Riny_van_Eekelen 

        Thanks for looking, I have no idea why the code does not work for me.  I will keep trying to find something.

    • jukapil's avatar
      jukapil
      MCT
      May 06, 2020

      WallisMcMath If you have used .Activate. Remove "Sheets("Groups").". It should work

      • WallisMcMath's avatar
        WallisMcMath
        Copper Contributor
        May 06, 2020

        jukapil 

        Thanks much for the response, but what you listed is what I tried.

        The code is in a CommandButton click event which is in the worksheet "Weekly" which is the Active Worksheet and the area to be selected is in Worksheet "Groups"

         

        I still have the error after removing "Sheets("Groups"). "

         

        The following  code Works

        Worksheets("Groups").Activate

        Sheets("Groups").Range("B2:L34").Select

         

        The following does not work

        Worksheets("Groups").Activate

        Sheets("Groups").Range(Cells(2,4), Cells(34,12)).Select   '(error occurs on this line of code)

        (I removed the variable names and replaced with numbers)

        The error message is Application-defined or object-defined error

         

        The following also does not work

        Worksheets("Groups").Activate

        Range(Cells(2,4), Cells(34,12)).Select   '(error occurs on this line of code)

        New Error Message of "Select Method of Range class failed"

         

Resources