RANGES IN VBA

INTRODUCTION

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

If you are extremely 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 object.

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

Its 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 the 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 usedRange(“K3”).SELECT. 

RUNNING THE CODE AND OUTPUT

SELECT RANGE AND CELLS IN VBA





COPY A RANGE AND PASTE IT AT OTHER LOCATION

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 very easy solution for 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 specially for the RANGE OBJECT.

The destination is going to be the first cell of the range. The destination can be same sheet, 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 the 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 same, the difference is just that we want to copy the range from First sheet to SHEET1 of the different workbook. 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 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 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 very easy solution for this problem.

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

Range.cut Destination

Range Just like COPY.

This is the method specially for the RANGE OBJECT.The destination is going to be the first cell of the range.The destination can be same sheet, 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 COPY, CUT works too for the ranges and cells on the different sheets.

Suppose everything is same, the difference is just that we want to cut the range from First sheet to SHEET1 of the different workbook. Let us try to copy the data from one sheet to another.

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

Range("A3", "D21").Cut 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 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 CUTindifferentworkbook()

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 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 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 RANGE object have a property known as END.END gets parameter inside it asEND(XLDIRECTION) where xlDIRECTION is the direction as xlUP, xlDOWN, xlTORIGHT , xlTOLEFT.

This property makes our task so easy that its done in seconds.so for the example , the syntax goes likeRANGE(“RANGE”).END(xlDIRECTION).

SELECTEND function finds out the last filled cell in a particular direction.

After this apply the Range selection method as discussed in 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 sub procedure.

First of all, just for information we use the method discussed above to find out the last filled cell in the down side.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.

Last cell came from the expressionand done. 

RUNNING THE CODE AND OUTPUT

SELECTION UPTO 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 UPTO THE CELL WHERE DATA IS AVAILABLE

PLANNING THE SOLUTION

In last example, we learnt 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 sub procedure and start the process.

We declare two integers and one string.

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

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 number 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.