RANGES IN VBA

Table of Contents

INTRODUCTION

RANGE OBJECT is again one of the most used objects in VBA.

If you are new to VBA, you are going to use it a lot.

Range refers to the CELL or GROUP OF CELL, where we will do everything like copying the data, pasting the data, inserting, retrieving, and many other operations.

So we must be aware of many small operations which are short but are of utmost importance.
In this article, we’d try to use many features, methods, and properties of Range objects.

WHAT IS A RANGE IN VBA?

RANGE in VBA is a cell or group of cells. The cells hold all the data as values or text which is known to us. We’ll try to learn many usable operations in Range objects.

Let us start.

SELECT CELLS IN EXCEL USING VBA

OBJECTIVE

SELECT CELLS FROM A3 TO D20  AND SELECT CELL K3 USING VBA IN EXCEL

USING VBA SELECT CELLS A3 TO D20

PLANNING THE SOLUTION

It’s a basic operation because every solution would start with selecting some cells.

Let us see how we can select them.

CODE

'GYANKOSH.NET
'SELECTING RANGE USING VBA DIFFERENT METHODS.

Sub selectionRangeExample()

Range("A3", "D20").Select

End Sub
'single cell selection

Sub selectionCELLExample()

Range("K3").Select

End Sub



EXPLANATION

There is just one statement in both the codes.Range(“A3”, “D20”) refers to the cell A3 AND D20.

SELECT makes Excel select the mentioned range.

For a single cell selection, we used

Range(“K3”).SELECT. 

RUNNING THE CODE AND OUTPUT

SELECT RANGE AND CELLS IN VBA

COPY A RANGE AND PASTE IT AT OTHER LOCATION IN VBA

OBJECTIVE-COPYING RANGE IN THE SAME SHEET

COPY THE RANGE FROM A2:D20 AND PASTE IT AT K2

PLANNING THE SOLUTION

Copying the range is going to be very frequent while using VBA.

But there is a very easy solution to this problem. There is a method for copying the range very easily.

The format is given by

Range.copy Destination Range

This is the method, especially for the RANGE OBJECT.

The destination is going to be the first cell of the range. The destination can be the same sheet, a different sheet, etc.

Let us try this in our code.

CODE

'gyankosh.net
'Copying the cells in the same sheet

Sub CopyinSheet()

Range("A2", "D20").Copy Range("K2") 'COPYING COMPLETE RANGE

Range("A5").Copy Range("F15") 'COPYING SINGLE ITEM

End Sub



EXPLANATION

We start with the declaration of a procedure.

For the copying, we use the method COPY of the range object as

Range(“A2”, “D20”).Copy Range(“K2”) ‘COPYING COMPLETE RANGE

*you can specify what range needs to be copied by changing the starting and ending addresses.

This statement shows that we select the range from A2 to D20 and copy it. After copying, paste it to the cells starting with K2.

Similarly, we tried to copy a single cell too with the statement

Range(“A5”). Copy Range(“F15”) ‘COPYING SINGLE ITEM

Here cell A5 is copied and pasted to F15.

RUNNING THE CODE AND OUTPUT

COPYING RANGES AND CELLS IN VBA


COPYING RANGE TO DIFFERENT SHEETS

Suppose everything is the same, the difference is just that we want to copy the range from the First sheet to SHEET1 of the different workbooks.

Let us try to copy the data from one sheet to another.

'gyankosh.net
'Copying the cells in the different sheet
Sub CopyinWorkbook()

Range("A2", "D20").Copy WorkSheets("Sheet3").Range("K2") 'COPYING COMPLETE RANGE TO SHEET3

End Sub

RESULT

The range will be copied to the sheet named SHEET3, in the same way, starting from K2.

If the sheet name is something else, use the sheet name in the target.

COPYING RANGE TO DIFFERENT WORKBOOK (WORKBOOK IS OPENED) IN VBA

Now going a step further, suppose we need to copy from the current workbook to another workbook. Here are the steps to copy range from one workbook to another workbook in VBA.

'gyankosh.net
'Copying the cells in the different workbooks.
Sub Copyindifferentworkbook()

Range("A2", "D20").Copy Workbooks("Book3.xlsx").Sheets("Sheet1").Range("K2") 'COPYING COMPLETE RANGE

End Sub

RESULT

The range will be copied to the sheet named SHEET1 in the workbook named “BOOK3.XLSX” in the sheet named SHEET1.

If the sheet name is something else, use the sheet name in the target.



CUT OR MOVE A RANGE OR CELL IN VBA

OBJECTIVE-MOVING A RANGE IN THE SAME SHEET

MOVE THE RANGE FROM A3:D21 AND MOVE IT AT K2

PLANNING THE SOLUTION

Moving/Cutting or copying is going to be a frequent operation while programming in VBA. But there is a very easy solution to this problem.

There is a method for copying the range very easily. The format is given by

Range.cut Destination

This is the method, especially meant for the RANGE OBJECT. The destination is going to be the first cell of the range. The destination can be the same sheet, a different sheet, etc. Let us try this in our code.

CODE

'gyankosh.net
'MOVING the cells in the same sheet
Sub CUTinSheet()
Range("A2", "D21").Cut Range("K2") 'CUTTING COMPLETE RANGE
End Sub



EXPLANATION

We start with the declaration of a procedure.

For the copying, we use the method CUT of the range object as

Range(“A2”, “D21”).Cut Range(“K2”) ‘CUT COMPLETE RANGE

*you can specify what range needs to be copied by changing the starting and ending addresses.

This statement shows that we select the range from A2 to D21 and cut it. After cutting paste it to the cells starting with K2.

Similarly, we can cut a single cell too with the statement

Range(“A5”).Cut Range(“F15”) ‘Cut a single cell value.

Here the cell A5 value will be moved to F15.

RUNNING THE CODE AND OUTPUT

Running this code will move the complete range of A3:D21 to a range starting from the cell K3.

*RUNNING PROCESS IS EXACTLY SAME AS PREVIOUS EXAMPLE THAT’S WHY NOT GIVEN HERE.

COPYING RANGE TO DIFFERENT SHEETS

Just like the method CUT, COPY works too for the ranges and cells on the different sheets.

Suppose everything is the same, the difference is just that we want to cut the range from the First sheet to SHEET1 of the different workbooks.

Let us try to copy the data from one sheet to another.

'gyankosh.net
'Copying the cells in the different sheet
Sub Copyindifferentsheets()

Range("A3", "D21").Copy WorkSheets("Sheet3").Range("K3") 'COPYING COMPLETE RANGE TO SHEET3

End Sub



RESULT

The range will be moved to the sheet named SHEET3 in the same way starting from K3.

If the sheet name is something else, use the sheet name in the target.

MOVING RANGE TO DIFFERENT WORKBOOK (WORKBOOK IS OPENED) IN VBA

Now going a step further, suppose we need to copy from the current workbook to another workbook. Here are the steps to copy range from one workbook to another workbook in VBA.

'gyankosh.net

'MOVING the cells in the different workbooks.

Sub COPYindifferentworkbook()

Range("A3", "D21").Copy Workbooks("Book3.xlsx").Sheets("Sheet1").Range("K3") 'COPYING COMPLETE RANGE

End Sub



RESULT

The range will be moved to the sheet named SHEET1 in the workbook named “BOOK3.XLSX” in the sheet named SHEET1.

If the sheet name is something else, use the sheet name in the target.

FIND OUT NEXT EMPTY CELL IN LEFT, RIGHT, UP, OR DOWN DIRECTIONS USING VBA

OBJECTIVE-FINDING OUT THE NEXT EMPTY CELL DOWN THE RANGE

SELECT THE DATA FROM A3 UP TO THE CELL WHERE DATA IS AVAILABLE.

PLANNING THE SOLUTION

FIRST OF ALL, FIND OUT THE LAST FILLED CELL. SELECT THE RANGE FROM A3 TO THE LAST FILLED CELL. 

Sometimes, a situation may occur when we don’t know the exact location of the data cells with content in it.

For such cases, the RANGE object has a property known as END.

END gets parameter inside it as END(XLDIRECTION) where xlDIRECTION is the direction as xlUP, xlDOWN, xlTORIGHT , xlTOLEFT.

This property makes our task so easy that it’s done in seconds. So for the example, the syntax goes like

RANGE(“RANGE”).END(xlDIRECTION).

After this apply the Range selection method as discussed in the first section of the same article. 

CODE


'gyankosh.net
'Copying the cells UTPO END OF DATA DOWNSIDE

Sub Selectionuptoend()

Range("A3").End(xlDown).Select 'finding out last filled cell

Range("A3", Range("A3").End(xlDown)).Select 'SELECTING THE RANGE FROM A3 TO LAST CELL

End Sub



EXPLANATION

We start with the declaration of the subprocedure.

First of all, just for information, we use the method discussed above to find out the last filled cell in the downside. After that in the next line, we use the RANGE SELECTION format which goes like

Range(“first cell”,”last cell”).select

First cell is given as A3.

The last cell came from the expression and done. 

RUNNING THE CODE AND OUTPUT

SELECTION UP TO THE AVAILABILITY OF DATA IN VBA

SIMILARLY, WE CAN CHOOSE ANY DIRECTION AND FIND OUT THE LAST CELL WITH CONTENT.



INSERT DATA AT THE END OF THE SHEET

OBJECTIVE-INSERTING THE DATA IN THE NEXT EMPTY CELL

SELECT THE DATA FROM A3 UP TO THE CELL WHERE DATA IS AVAILABLE

PLANNING THE SOLUTION

In the last example, we learned to find out the last filled cell or the last cell with content. We‘ll make use of the method END here.

We’ll find the last content holding cell in the column 1 and find out the number of rows.

After this, we’ll shift the focus to next cell and enter the value there.

Finding row number

Range(“A3”).End(xlDown).Row

CODE

'gyankosh.net

Sub enterdatainlastrow()

Dim temp As String 'The text to be entered’

temp = " Hello Everyone"

Dim lastrow As Integer, lastcolumn As Integer

lastrow = Range("A3").End(xlDown).Row 'finding out row number of last filled cell

lastcolumn = Range("A3").End(xlToRight).Column 'finding out the column number of last filled cell

Cells(lastrow + 1, 1) = temp 'putting the string to the new cell.

End Sub



EXPLANATION

We declare the subprocedure and start the process.

We declare two integers and one string.

integers to hold the last used row number and last used column number.

The string is given as HELLO EVERYONE which is passed to it.

We check the last used column number and row number using the following methods.

lastrow = Range(“A3”).End(xlDown).Row ‘finding out row number of last filled cell
lastcolumn = Range(“A3”).End(xlToRight).Column ‘finding out the column number of last filled cell

Both the statements find the last used row number and last used column number.

After this, we put the value in the cell, whose row index is rownumber +1 as rownumber is filled and column number is 1.

We have not used column numbers but it can be used. It was just for a demonstration.

RUNNING THE CODE AND OUTPUT

ENTER THE DATA IN THE NEXT AVAILABLE CELL

SIMILARLY WE CAN CHOOSE ANY SIDE FOR THE CONTENT ENTRY, IT MAY BE LEFT , RIGHT , UP OR DOWN.