How to Select Cells and Ranges in Excel Visual Basic

Step-by-Step Guide

1. 1

   Step 1: Use the Visual Basic methods listed in this table:

   The first example shown above will return an error if the active cell is in columns A through D, since moving four columns to the left would take the active cell to an invalid cell address. ,,,,, Note also that the Union method does not work across sheets.
   
   For example, this line works fine Set y = Application.Union(Range("Sheet1!A1:
   B2"), Range("Sheet1!C3:
   D4")) but this line Set y = Application.Union(Range("Sheet1!A1:
   B2"), Range("Sheet2!C3:
   D4")) returns the error message:
   Union method of application class failed ,, Each example states the range of cells in the sample data that would be selected.,,, _ End(xlDown).Address).Select When this code is used with the sample table, cells A1 through A4 will be selected. , _ End(xlUp).Address).Select When this code is used with the sample table, it will select cells A1 through A6. , The range selected by the CurrentRegion method is an area bounded by any combination of blank rows and blank columns.
   
   The following is an example of how to use the CurrentRegion method:
   ActiveSheet.Range("a1").CurrentRegion.Select This code will select cells A1 through C4.
   
   Other examples to select the same range of cells are listed below:
   ActiveSheet.Range("A1"
   
   _ #*ActiveSheet.Range("A1").End(xlDown).End(xlToRight)).Select or ActiveSheet.Range("A1:" & _ ActiveSheet.Range("a1").End(xlDown).End(xlToRight).Address).Select In some instances, you may want to select cells A1 through C6.
   
   In this example, the CurrentRegion method will not work because of the blank line on Row
   5.
   
   The following examples will select all of the cells: lastCol = ActiveSheet.Range("A1").End(xlToRight).Column lastRow = ActiveSheet.Cells(65536, lastCol).End(xlUp).Row ActiveSheet.Range("A1"
   
   ActiveSheet.Cells(lastRow, lastCol)).Select or lastCol = ActiveSheet.Range("a1").End(xlToRight).Column lastRow = ActiveSheet.Cells(65536, lastCol).End(xlUp).Row ActiveSheet.Range("a1:" & _ ActiveSheet.Cells(lastRow, lastCol).Address).Select ,, Herewith is an image of a non-contiguous selection in Excel. , To select, you would type aCell.
   
   Select before the End If.
   
   See the article How to Use "Find" in Excel VBA Macros for exactly that modification to the code herewith supplied.,, Record a dummy macro by choosing Developer on the Ribbon and then hitting the Record icon and just selecting cell A1, and then Stop Recording.,,,,,, See the aforementioned article regarding other types.
   
   UserRange.Clear UserRange.Select Canceled:
   End Sub ,,,
2. 2

   Step 2: Use the Visual Basic properties in this table:

3. 3

   Step 3: Use either of the following examples to select cell E6 on the active worksheet: ActiveSheet.Cells(6

4. 4

   Step 4: 5).Select -or- ActiveSheet.Range("E6").Select

5. 5

   Step 5: Use either of the following examples to select cell E6 on another worksheet in the same workbook: Application.Goto ActiveWorkbook.Sheets("Sheet2").Cells(6

6. 6

   Step 6: 5) -or- Application.Goto (ActiveWorkbook.Sheets("Sheet2").Range("E6")) Or

7. 7

   Step 7: activate the worksheet

8. 8

   Step 8: then use the method above to select the cell: Sheets("Sheet2").Activate ActiveSheet.Cells(6

9. 9

   Step 9: 5).Select

10. 10

    Step 10: Use either of the following examples: to select cell A2 on a worksheet in a different workbook: Application.Goto Workbooks("BOOK2.XLS").Sheets("Sheet1").Cells(2

11. 11

    Step 11: 1) or Application.Goto Workbooks("BOOK2.XLS").Sheets("Sheet1").Range("A2") Or

12. 12

    Step 12: activate the worksheet

13. 13

    Step 13: then use method 1 above to select the cell: Workbooks("BOOK2.XLS").Sheets("Sheet1").Activate ActiveSheet.Cells(2

14. 14

    Step 14: 1).Select

15. 15

    Step 15: Use any of the following examples to select the range C1:D6 on the active worksheet: ActiveSheet.Range(Cells(1

16. 16

    Step 16: Cells(6

17. 17

    Step 17: 4)).Select ActiveSheet.Range("C1:D6").Select ActiveSheet.Range("C1"

18. 18

    Step 18: "D6").Select

19. 19

    Step 19: Use either of the following examples to select the range C3:E11 on another worksheet in the same workbook: Application.Goto ActiveWorkbook.Sheets("Sheet3").Range("C3:E11") Application.Goto ActiveWorkbook.Sheets("Sheet3").Range("C3"

20. 20

    Step 20: "E11") Or

21. 21

    Step 21: activate the worksheet

22. 22

    Step 22: then use the method above to select the range: Sheets("Sheet3").Activate ActiveSheet.Range(Cells(3

23. 23

    Step 23: Cells(11

24. 24

    Step 24: 5)).Select

25. 25

    Step 25: Use either of the following examples to select the range E12:F12 on a worksheet in a different workbook: Application.Goto Workbooks("BOOK2.XLS").Sheets("Sheet1").Range("E12:F12") Application.Goto _ Workbooks("BOOK2.XLS").Sheets("Sheet1").Range("E12"

26. 26

    Step 26: "F12") Or

27. 27

    Step 27: activate the worksheet

28. 28

    Step 28: then use the method above to select the range: Workbooks("BOOK2.XLS").Sheets("Sheet1").Activate ActiveSheet.Range(Cells(12

29. 29

    Step 29: Cells(12

30. 30

    Step 30: 6)).Select

31. 31

    Step 31: Use either of the following examples to select the named range "Test1" on the active worksheet: Range("Test1").Select Application.Goto "Test1"

32. 32

    Step 32: Use the following example to select the named range "Test2" on another worksheet in the same workbook: Application.Goto Sheets("Sheet3").Range("Test2") Or

33. 33

    Step 33: activate the worksheet

34. 34

    Step 34: then use the method above to select the named range: Sheets("Sheet3").Activate Range("Test2").Select

35. 35

    Step 35: Use the following example to select the named range "Test3" on a worksheet in a different workbook: Application.Goto _ Workbooks("BOOK2.XLS").Sheets("Sheet4").Range("Test3") Or

36. 36

    Step 36: activate the worksheet

37. 37

    Step 37: then use the method above to select the named range: Workbooks("BOOK2.XLS").Sheets("Sheet4").Activate Range("Test3").Select

38. 38

    Step 38: Use the following example to select a cell that is three rows below and four columns to the left of the active cell: ActiveCell.Offset(3

39. 39

    Step 39: -4).Select Use the following example to select a cell that is one row above and three columns to the right of the active cell: ActiveCell.Offset(-1

40. 40

    Step 40: 3).Select Note An error will occur if you try to select a cell that is "off the worksheet."

41. 41

    Step 41: Use either of the following examples to select a cell that is five rows below and four columns to the right of cell C7: ActiveSheet.Cells(7

42. 42

    Step 42: 3).Offset(5

43. 43

    Step 43: 4).Select ActiveSheet.Range("C7").Offset(5

44. 44

    Step 44: 4).Select

45. 45

    Step 45: Use the following example to select a range of cells that is the same size as the named range "Test5" but that is shifted four rows down and three columns to the right: ActiveSheet.Range("Test5").Offset(4

46. 46

    Step 46: 3).Select If the named range is on another (not the active) worksheet

47. 47

    Step 47: activate that worksheet first

48. 48

    Step 48: and then select the range using the following example: Sheets("Sheet3").Activate ActiveSheet.Range("Test").Offset(4

49. 49

    Step 49: 3).Select

50. 50

    Step 50: Use the following example to select the named range "Database"

51. 51

    Step 51: then extend the selection by five rows: Range("Database").Select Selection.Resize(Selection.Rows.Count + 5

52. 52

    Step 52: _Selection.Columns.Count).Select

53. 53

    Step 53: Use the following example to select a range four rows below and three columns to the right of the named range "Database" and include two rows and one column more than the named range: Range("Database").Select Selection.Offset(4

54. 54

    Step 54: 3).Resize(Selection.Rows.Count + 2

55. 55

    Step 55: _Selection.Columns.Count +1).Select

56. 56

    Step 56: Use the following example to select the union (that is

57. 57

    Step 57: the combined area) of the two named ranges "Test" and "Sample

58. 58

    Step 58: ": Application.Union(Range("Test")

59. 59

    Step 59: Range("Sample")).Select Note that both ranges must be on the same worksheet for this example to work.

60. 60

    Step 60: Use the following example to select the intersection of the two named ranges "Nest" and "Eggs": Application.Intersect(Range("Nest")

61. 61

    Step 61: Range("Eggs")).Select Note that both ranges must be on the same worksheet for this example to work.

62. 62

    Step 62: The following five examples in this article refer to the following sample set of data.

63. 63

    Step 63: Use the following example to select the last cell in a contiguous column: ActiveSheet.Range("A1").End(xlDown).Select When this code is used with the sample table

64. 64

    Step 64: cell A4 will be selected.

65. 65

    Step 65: Use the following example to select the cell below a range of contiguous cells: #*ActiveSheet.Range("A1").End(xlDown).Offset(1

66. 66

    Step 66: 0).Select When this code is used with the sample table

67. 67

    Step 67: cell A5 will be selected.

68. 68

    Step 68: Use use one of the following examples to select a range of contiguous cells in a column: ActiveSheet.Range("A1"

69. 69

    Step 69: ActiveSheet.Range("a1").End(xlDown)).Select or ActiveSheet.Range("A1:" & ActiveSheet.Range("A1").

70. 70

    Step 70: Use one of the following examples to select a range of cells that are non-contiguous: ActiveSheet.Range("A1"

71. 71

    Step 71: ActiveSheet.Range("A65536").End(xlUp)).Select #** or ActiveSheet.Range("A1:" & ActiveSheet.Range("A65536").

72. 72

    Step 72: Use the CurrentRegion method in order to select a rectangular range of cells around a cell.

73. 73

    Step 73: Use the following sample table and macro example to select multiple non-contiguous columns of varying length:

74. 74

    Step 74: StartRange = "A1" EndRange = "C1" Set a = Range(StartRange

75. 75

    Step 75: Range(StartRange).End(xlDown)) Set b = Range(EndRange

76. 76

    Step 76: Range(EndRange).End(xlDown)) Union(a

77. 77

    Step 77: b).Select When this code is used with the sample table

78. 78

    Step 78: cells A1:A3 and C1:C6 will be selected.

79. 79

    Step 79: Below is a macro which builds cell references

80. 80

    Step 80: and although it does not select them

81. 81

    Step 81: it does perform other useful tasks with them.

82. 82

    Step 82: Set up a workbook with Sheet1 having in cells A1 to A60000 the values 1 to 60000.

83. 83

    Step 83: In the Excel Menu

84. 84

    Step 84: Preferences

85. 85

    Step 85: set the Ribbon Developer to checked or On

86. 86

    Step 86: so you can work with macros.

87. 87

    Step 87: Copy the macro below using Advanced Editing to a text processor like MS Word and do a REPLACE ALL for "#** " (w/o the quotes and with the trailing space) over all the entire selection.

88. 88

    Step 88: Copy the selection and paste it over your dummy macro in Excel by choosing the (VB) EDIT(OR) icon button under Developer on the Ribbon.

89. 89

    Step 89: Run the macro by choosing menuitem Tools .. Macros .. and the name of the macro

90. 90

    Step 90: Create a button by selecting the button icon to the right of the Record icon

91. 91

    Step 91: and assigning your macro to it

92. 92

    Step 92: then pressing the button on your worksheet to run the macro.

93. 93

    Step 93: Here's the macro to do the REPLACE ALL on (down to End Sub): Sub CommandButton2_Click() ‘ Assign this macro to a button you create Dim oSht As Worksheet Dim lastRow As Long

94. 94

    Step 94: i As Long Dim strSearch As String Dim aCell As Range Set oSht = Sheets("Sheet1") lastRow = oSht.Range("A" & Rows.Count).End(xlUp).Row strSearch = "10000" Set aCell = oSht.Range("A1:A" & lastRow).Find(What:=strSearch

95. 95

    Step 95: _ LookIn:=xlValues

96. 96

    Step 96: LookAt:=xlPart

97. 97

    Step 97: SearchOrder:=xlByRows

98. 98

    Step 98: _ SearchDirection:=xlNext

99. 99

    Step 99: MatchCase:=False

100. 100

     Step 100: SearchFormat:=False) If Not aCell Is Nothing Then MsgBox "Value Found in Cell " & aCell.Address End If Exit Sub End Sub

101. 101

     Step 101: Here's another macro

102. 102

     Step 102: instead acquiring a range from the user and erasing it prior to selecting it: Sub EraseRange() Dim UserRange as Range On Error GoTo Canceled Set UserRange = Application.InputBox _ (Prompt :="Range to erase:"

103. 103

     Step 103: _ Title :="Range Erase"

104. 104

     Step 104: _ Default :=Selection.Address

105. 105

     Step 105: _ Type := 8) ' Type 8 refers to a cell reference

106. 106

     Step 106: as a range object. '

107. 107

     Step 107: The user will select the range with the mouse and/or shift key and the absolute reference will appear in the Input Box prior to erasure.

108. 108

     Step 108: This sort of macro can also be used to fill a range with random numbers

109. 109

     Step 109: Watch as much of the following video as you care to

110. 110

     Step 110: and then come back here by using your ESC key or the BACK button in your browser while in YouTube.

Detailed Guide

About the Author