VBA-Excel: Create a WorkBook at Runtime.
Using VBA (Visual Basic for Applications) , you can create Excel workbook at the runtime by using Add() method. If you won’t provide any name for the workbook it will be created as BookN,...
Category: Excel WorkBook
VBA-Excel: CurrentRegion
CurrentRegion analyses the filled cells and the blank cells surrounded by the rangeyou provide. Its returns a Range object as a region in Mxn shape. Syntax: RangeObject. CurrentRegion Example: Function FnCurrentRegion() Dim mainWorkBook As...
VBA-Excel: Cells Ranges Offset – Active Cell
ActiveCell, as the name clearly indicates that the cell which is presently active in your worksheet, in other words you can if u start typing the value u entered will go to active cell....
VBA-Excel : 3D-Ranges – FillAcrossSheets Method
By using FillAcrossSheets you can copy the Range of one worksheet to other multiple worksheets. Steps: Create an array and assign multiple worksheets names ( in which the data to be copied), as...
VBA-Excel: Select and Activate Cells – Select
Select: You can Select a cells or cells for performing many activities like putting some value in cell or doing formatting or copy-paste the data. You can put or enter values in cells with/without...
VBA Excel – Looping Through a Range of Cells
Referring Excel cells using For…Next -Loop For i = 1 to 10 Range(“A” & i).Value = 1 Next Enter ‘1’ in Cells from A1 to A10. Referring Columns of excels using For-Loop For i...
VBA Excel – Refer to Multiple Ranges : Union Method
You can combine more than one Ranges into one Range using Union Method and later on instead of writing all the ranges repeatedly, just use the combined one. Example Sub CombileMultipleRange() Dim objCombinedR as...
VBA Excel – Cells, Ranges and Offset: Refer to Cells by Using Shortcut Notation
You don’t have to type Range every time, you have shortcut for that, ‘Brackets’. Shortcut for referring one cell. Worksheets(“Sheet1”).[G5].Interior.ColorIndex = 45 Instead of using Range(“G5”), you can use [G5]. Shortcut for referring range...
VBA Excel – Cells, Ranges and Offset: Refer Range by using A1 Notations
In the earlier tutorial (Range), you have seen how you use Range property. In this tutorial you will see the many different ways to use Range property to refer the excel sheet’s rows and...
VBA-Excel: Copy/Paste data – Copy the Entire data from one sheet to another
In VBA-Excel, Copy and paste the data plays an important role, we can copy data from one place and paste it at some other place in the same way like how we do it...
VBA-Excel: Copy/Paste data – Copy the Entire row data and paste it to another row
In VBA-Excel, Copy and paste the data plays an important role, we can copy data from one place and paste it at some other place in the same way like how we do it...
VBA-Excel: Copy/Paste data – Copy the data from a call and paste it to another cell
In VBA-Excel, Copy and paste the data plays an important role, we can copy data from one place and paste it at some other place in the same way like how we do it...
VBA-Excel: Clear data from Excel Sheet(WorkSheet)
VBA-Excel: Clear data from Excel Sheet(WorkSheet) Format: WorkBook.WorkSheet.Range.Clear Examples: mainworkBook.Sheets(3).UsedRange.Clear Clear data from particular cell: Dim mainworkBook As Workbook Set mainworkBook = ActiveWorkbook mainworkBook.Sheets(“MyFirstMacro”).Range(“A2”).Clear
VBA-Excel: UsedRange
In VBA-Excel, UsedRange is very effective property when it comes to deal with the excel file which contains data. Right from formatting options of excel sheet to copy pasting the data, getting informations about...
VBA-Excel: Get the names of all WorkSheets in a Excel (WorkBook)
Open a new Excel WorkBook and press “Alt+F11” to open the Visual Basic Editor Copy Paste the following code Sub FnGetSheetsName() Dim mainworkBook As Workbook Set mainworkBook = ActiveWorkbook For i = 1 To...
VBA-Excel: Delete Blank Rows from Excel Work Sheet
Sometimes deleting the blank rows from you Excel sheet is a tedious task to do especially when your sheet contains lots of data, say 10k-15k rows and having some blank rows in between...
VBA Excel – Cells, Ranges and Offset : Offset
Offset: Offset property in VBA-Excel is used along with Range. With the help of Offset property user can move around in the sheet. Format: Range(“StartingPoint”).Offset(NoOfRowsToBeMoved , NoOfColumnsToBeMoved) Example : Range(“A2”).Offset(1,0).Select And compiler reads, move...
VBA Excel – Cells, Ranges and Offset : Range
Format: Range (“ColumnNameRowNumber”) Ex: In VBA-Excel when you say Range(“A5”) it’s very easy to understand that you are talking about 5th row in 1st column (“A”) As the name states Range, you can select...
Message Boxes in VBA Excel (Msgbox)
In VBA Excel Macro, Message Box (Msgbox) is one of the important features. Right from warning messages to calculated values in your code or anything. Whenever the code has something to communicate to the...
FileSystemObject:OpenTextFile Method
FileSystemObject:OpenTextFile Method Description : This function opens a file and returns a TextStream object and later you can use that TextStream object to perform various activities on that file like reading or writing Format...