
PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES
CONTENTS
RANGES IN VBA
INTRODUCTION
WHAT IS A RANGE IN VBA
SELECT CELLS IN EXCEL USING VBA
OBJECTIVE
USING VBA SELECT CELLS A3 TO D20
PLANNING THE SOLUTION
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
RUNNING THE CODE AND OUTPUT
COPY A RANGE AND PASTE IT AT OTHER LOCATION
OBJECTIVE-COPYING RANGE IN THE SAME SHEET
PLANNING THE SOLUTION
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 selec 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 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
PLANNING THE SOLUTION
This is the method specially for the RANGE OBJECT.
CODE
‘gyankosh.net
‘MOVING the cells in the same sheet
Sub CUTinSheet()
Range(“A2”, “D21”).Cut Range(“K2”) ‘CUTING 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”) ‘CUTING 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”) ‘Cutting 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
PLANNING THE SOLUTION
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
RUNNING THE CODE AND OUTPUT
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
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 rownumber
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
SIMILARLY WE CAN CHOOSE ANY SIDE FOR THE CONTENT ENTRY, IT MAY BE LEFT , RIGHT , UP OR DOWN.
OTHER WAYS TO REACH THIS ARTICLE
WHAT IS EXCEL, HOW EXCEL WORKS, WRITE FORMULA IN EXCEL, EXAMPLE OF USING FORMULA IN EXCEL, ANIMATED EXAMLPE OF USING FORMULA IN EXCEL
YOU MAY LIKE
- HOW TO TROUBLESHOOT A FORMULA IN EXCEL?
- USE OF FONTS, FONT SIZE, INDENTATION, TEXT TO THE RIGHT, TEXT TO THE LEFT, TEXT IN THE MIDDLE ETC.
- how to insert table in excel?
- FOR MORE ARTICLES, CLICK EXCEL OR TYPE IN SEARCH BELOW.