Saltar al contenido
PROGRAMAR EN VBA MACROS DE EXCEL

How to select or reference cells with Excel macro


.

When Excel programming with VBA, it is always necessary to select or reference to sheets, cells, rows, columns or ranges for an Excel macro or VBA, here I present different ways of select or reference, in the post presents also as reference or select a cell using variables.

Examples of macros that have been published on this website; such as copy multiple selected items from a listbox to cells in Excel, or how to fill a listbox depending on the selected item in a combo box, or how to fill a listbox which depends on another listbox and a combobox.

You can see the frequent use of the selection or cell reference, which is very common to all Excel macro or VBA for Excel procedure that is developed using Visual Basic for Applications or VBA; reference to cells also use when working with userform, this can be seen in the macro calculates a balance between time and date or this procedure to search for data in three columns, are a number of procedures requiring  know reference or select cells, I present below the most common ways to select or reference cells excel with macro

Need to read an excellent book on Excel that will help operate the spreadsheet, click here, if you want to learn about Excel, in Spanish, then you must click here. If what you need is to learn or deepen about programming macros with VBA, this is one of the best courses on line I’ve seen on the internet.

  
The following videos visually exposed explanation of each of the codes:

Here are the different codes on how to reference or select a sheets, cells, rows, columns or ranges with explanation; related to the different forms of reference or select cells you can be useful exposed post  how copying, cut and paste data into cells with a macro using VBA.

‘Refer to all sheets in the active workbook
ActiveWorkbook.Sheets.Select

http://share.payoneer-affiliates.com/a/clk/2mhvGX
MASTERCARD PAYONEER

‘To refer to Sheet2
Sheets(«Hoja2»).Select

‘Referencing the sheet is in position 2 regardless of the name
Sheets(2).Select

‘Refer to the previous sheet to the active tab
ActiveSheet.Previous.Select

‘Refer to the back sheet to the active tab
ActiveSheet.Next.Select

‘To refer to a cell in another book
Application.Goto Workbooks(«Libro1.xlsm»).Sheets(«Sheet1»).Cells(7, 6)

‘Referencing a sheet storing in a variable the name of the active sheet, prior to active and back to active
Dim sheetnom As String
sheetnom = ActiveSheet.Name
sheetnom1 = ActiveSheet.Previous.Name
sheetnom2 = ActiveSheet.Next.Name

‘Referencing a sheet storing in a variable sheet number, current, prior to the current and next
Dim sheetnumb As Byte
sheetnumb = ActiveSheet.Index
sheetnumb = ActiveSheet.Previous.Index
sheetnumb = ActiveSheet.Next.Index

‘Select cell C8 of sheet1
Sheets(«hoja1»).Select
Range(«C8»).Select

‘Referencing otherwise the same cell C8
Cells(8, 3).Select

‘Another way to refer to cell C8
Cells(8, «C»).Select

We can also refer to cell C8 but leaf 2
Sheets(«hoja2»).Select
Cells(8, 3).Select

‘You can select the cell c8 of the active sheet by
ActiveSheet.Cells(8, 3).Select

‘Select the continuous range C2 to D10
Range(«C2:D10»).Select

Reference otherwise the C2 range: D10
Range(Cells(2, 3), Cells(10, 4)).Select

‘Reference otherwise the C2 range: D10
Range(«C2», «D10»).Select

‘Select all worksheet cells
Cells.Select

‘Copy all cells in Sheet1 to Sheet2
Sheets(«hoja1»).Cells.Copy Destination:=Sheets(«hoja1»).Range(«A1»)

                                                                                   

‘Select entire column B
Range(«A:A»).Select

‘Select all columns from column A to column C
Range(«A:C»).Select

‘Select the entire row 4
Range(«4:4»).Select

‘Select all the rows from row 1 to row 5
Range(«1:5»).Select

‘Select the range discontinuous C5 to G5; A6; F3 to H6 and C24
Range(«C5:G5,A6,F3:H3,C24»).Select

‘Select the union of two or more specified ranges, similar to select and have tight control key
Application.Union(Range(«A2: C5»), Range(«F6: H12»)).Select

‘Select two columns of non-contiguous columns of varying length
Set a = Range(«A1», Range(«A1»).End(xlDown))
Set b = Range(«E1», Range(«E1»).End(xlDown))
Union(a, b).Select

‘Select a higher range to previously selected, the range is extended to 10 rows, 4 columns
Range(«D10»).Select
Selection.Resize(10, 4).Select

‘Select a greater range to previously selected by setting the number of row and columns to select additional
Range(«C2:D10»).Select
Selection.Resize(Selection.Rows.Count + 5, Selection.Columns.Count + 4).Select

‘Select from cell E2 to the last cell with data down, similar to ctrl + shift + arrow down
Range(«E2», Range(«E2»).End(xlDown)).Select

‘Another form to select
Range(«E2:» & Range(«E2»).End(xlDown).Address).Select

‘Select from cell E2 to the last cell data to the right, similar to ctrl + shift + right arrow
Range(«E2», Range(«E2»).End(xlToRight)).Select

‘Another form to select
Range(«E2:» & Range(«E2»).End(xlToRight).Address).Select

 

‘Select from cell E2 to the last cell data to the left, similar to ctrl + shift + left arrow
Range(«E2», Range(«E2»).End(xlToLeft)).Select

‘Another form to select
Range(«E2:» & Range(«E2»).End(xlToLeft).Address).Select

‘Select from cell E2 to the first cell with data from below, similar to ctrl + shift + arrow up
Range(«E2», Range(«E1048576»).End(xlUp)).Select

‘Another form to select
Range(«E2:» & Range(«E1048576»).End(xlUp).Address).Select

‘Select by variables and cells to the right range
Sheets(«ApiladoVertical»).Range((Cells(filaAV, 1)), Range((Cells(filaAV, 1)), Cells(filaAV, 1)).End(xlToRight)).Select

‘Select the last cell with data down, similar to ctrl + arrow down
Range(«E2»).End(xlDown).Select

‘Select the last cell with data to the right, similar to ctrl + right arrow
Range(«E2»).End(xlToRight).Select

‘Select the last cell with data to the left, similar to ctrl + left arrow
Range(«E2»).End(xlToLeft).Select

‘Select from the cell E 1048576 the first cell with data upward, similar to ctrl + arrow up
Range(«E1048576»).End(xlUp).Select

‘Refer to the last cell without data
ActiveSheet.Range(«a1»).End(xlDown).Offset(1, 0).Select

‘Refer to the last column no data
ActiveSheet.Range(«a1»).End(xlDown).Offset(0, 1).Select

‘Refer to the address of the last data cell down
Dim addr As String
Range(«E2»).End(xlDown).Select
‘Absolute address (Ex: $E$50)
addr = Selection.Cells(1, 1).Address
‘Relative  address  (Ex: B50)
addr = Selection.Cells(1, 1).Address(False, False)

‘Refer to the address of the last cell to the right data
Dim addrc As String
Range(«E2»).End(xlToRight).Select
‘Absolute address (Ex: $E$50)
addrc = Selection.Cells(1, 1).Address
‘Relative  address  (Ex: B50)
addrc = Selection.Cells(1, 1).Address(False, False)

https://aplicaexcelcontable.com/Alumnos/aplica-excel-contable-2?hop=marcrodos1
CURSO DE MACROS

‘Referencing the number of the last row with continuous data
Dim uf As String
Range(«E2»).End(xlDown).Select
uf = ActiveCell.Row

‘Referencing the number of the last column with continuous data
Dim uc As String
Range(«E2»).End(xlToRight).Select
uc = ActiveCell.Column

‘Refer to the last row with data
uf = Sheets(«hoja2»).Range(«A» & Rows.Count).End(xlUp).Row

«Referencing to the last row with data in column other form
uf = Cells(1048576, 1).End(xlUp).Row

«Referencing to the last column with data in row 1 otherwise
uc = Cells(1, 16384).End(xlToLeft).Column

‘Select and combine the selected cells
Range(«B1:E1»).Select
Selection.Merge

‘Select and break apart the selected cells
Range(«B1:E1»).Select
Selection.UnMerge

‘Select a range where the active cell is
Range(«B2»).CurrentRegion.Select

‘Another way to select a range where the active cell is
ActiveSheet.Range(«e2», ActiveSheet.Range(«e2»).End(xlDown).End(xlToRight)).Select

‘Another way to select a range where the active cell is
ActiveSheet.Range(«e2:» & ActiveSheet.Range(«e2»).End(xlDown).End(xlToRight).Address).Select

‘Select a range where the active cell is located, but having a row with no data in the range
lastCol = ActiveSheet.Range(«E2»).End(xlToRight).Column
lastRow = ActiveSheet.Cells(E1048576, lastCol).End(xlUp).Row
ActiveSheet.Range(«E2», ActiveSheet.Cells(lastRow, lastCol)).Select

‘Select the last cell with data regardless if there are empty cells in the middle, in other words
‘Selects the last cell in the range formed between the last data column and last row with data
ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Select

‘Select the last cell with data regardless if there are empty cells in the middle
Dim addr1 As String
ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Select
Selection.End(xlToLeft).Select
addr1 = Selection.Cells(1, 1).Address(False, False)

‘Refer to the last column no data
‘Select the last no data regardless if there are empty cells in the middle
ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Select
Selection.End(xlToLeft).Offset(1, 0).Select

‘Referencing entire range data regardless if there are empty cells in the middle
Dim addr2 As String
ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Select
addr2 = Selection.Cells(1, 1).Address(False, False)
Range(«A1:» & addr2).Select

In the video you will find the first part of the explanation on how to select or referenced cells in Excel.

‘Referencing and select the entire range data regardless of whether there are empty cells in the middle and define name the range
‘It’s like going to the menu formulas, naming and then define name.
Dim addr3 As String
ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Select
addr3 = Selection.Cells(1, 1).Address(False, False)
Range(«A1:» & addr3).Select
ActiveWorkbook.Names.Add Name:=»myrange», RefersTo:=»=Hoja2!» & Selection.Address
Range(«myrange»).Select

‘Referencing a cell with a certain value over the entire range that contains data
Dim addrt, addrsel As String
Dim cell As Range
Dim searchvalue As Integer
ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Select
addrt = Selection.Cells(1, 1).Address(False, False)
Range(«A1:» & addrt).Select
searchvalue = 15
For Each cell In Selection
If cell = searchvalue Then addrsel = cell.Address(False, False)
Next cell
Range(addrsel).Select
addrt = Selection.Address(False, False)

‘Select a range using a variable that determines the range
Dim Ran1 As String
Ran1 = «B5:C18»
Range(Ran1).Select

‘Select the cell using a variable that gets its value in cell B5
Dim file As String
file = Range(«B5»).Value
ActiveSheet.Select
Range(«B» & file).Select

‘Select cell A2 by a variable whose value is given
Dim file1 As Integer
file1 = 2
Range(«A» & file1).Select

‘Select a range whose last row is given by the value of a cell
Dim filfree As Integer
filfree = Range(«C1»).Value
Range(«A1» & «:E» & filfree).Select

‘Select a range whose last column is given by the value of a cell
Dim colfree As Integer
colfree = Range(«A28»).Value
Range(Cells(3, 2), Cells(5, colfree)).Select

‘Select a range whose last column is given by the value of the current cell
Dim colfree1 As Integer
colfree1 = ActiveCell.Column
Range(«B2» & «:E» & colfree1).Select

‘Select as on cells that is some distance from the active cell, in other words to many rows and many columns of the current cell.
‘Select from A1 8 rows and 15 columns down to the right
Sheets(«hoja2»).Range(«A1»).Offset(8, 15).Select

‘Selects previous 3 rows and 1 column to the left of the current cell
ActiveSheet.Range(«h10»).Offset(-3, -1).Select

‘Select from the active cell down 4 rows and 2 columns to the right
ActiveCell.Offset(4, 2).Select

‘Selects previous rows 3 and 2 column to the left of the current cell
ActiveCell.Offset(-3, -2).Select

‘Select cells in a relative other than the current cell
ActiveSheet.Range(«C7»).Offset(5, 4).Select

‘You can also use
ActiveSheet.Cells(7, 3).Offset(5, 4).Select

‘Select a specified range, then move the selection in a relative and resize the selection
Range(«C2:E5»).Select
Selection.Offset(4, 3).Resize(Selection.Rows.Count + 2, Selection.Columns.Count + 1).Select

               
‘Count the number of rows with continuous data
Dim nfil  As Integer
Range(«E2», Range(«E2»).End(xlDown)).Select
nfil = Selection.Rows.Count

‘Count the number of columns with continuous data                               
Dim ncol As Integer
Range(«E2», Range(«E2»).End(xlToRight)).Select
nrocol = Selection.Columns.Count

‘Count the number of cells in a range
Dim nceldas As Integer
Range(«E2»).CurrentRegion.Select
nceldas = Selection.Cells.Count

‘Select the intersection of two or more ranges, a range must be intersected with another but fails
Application.Intersect(Range(«A2:C5»), Range(«B2:C4»)).Select

Si te fue de utilidad puedes INVITARME UN CAFÉ y de esta manera ayudar a seguir manteniendo la página, CLICK para descargar en ejemplo en forma gratuita.


.

If this post was helpful INVITE ME A COFFEE and so help keep up the page, CLICK to download free example.