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.
This is the part II of the topic.
We’ll see some more functionalities here which we can use in our applications.
COUNT THE NUMBER OF SELECTED CELL IN VBA
We already have learnt how to select a cell or a range as per our choice. Now there are a few more simple functions which can be very helpful for us while programming VBA.
So let us see how we can count the selected cells.
Suppose we have selected a range from A2: D20
Let us count the selected cells.
Range(“A2″,”D20”).SELECT
SELECTION.COUNT
COUNT THE NUMBER OF ROWS IN SELECTED CELL IN VBA
Like the previous , simple count of the selected cell. It is also too easy to find out the number of Rows in the selection of cell in VBA.
We may need such functions if we need to do something after the selection or with the selection when we need to know the number of rows.
SYNTAX TO KNOW THE NUMBER OF ROWS IN SELECTION OF CELLS IN VBA:
Range(“A2″,”D20”).SELECT
SELECTION.ROWS.COUNT
COUNT THE NUMBER OF COLUMNS IN SELECTED CELL IN VBA
Like the row count, column count can also be done with the selected cells. It is also too easy to find out the number of columns in the selection of cell in VBA. We may need such functions if we need to do something after the selection or with the selection when we need to know the number of columns.
SYNTAX TO KNOW THE NUMBER OF COLUMNS IN SELECTION OF CELLS IN VBA:
Range(“A2″,”D20”).SELECT
SELECTION.COLUMNS.COUNT
COUNT THE NUMBER OF CELL IN A RANGE IN VBA
In the previous section we learnt to get different counts from the selection object of a range selected.
Now let us try to find out the same counts of the simple RANGE OBJECT.
Let us find the count of the cells first.
The syntax for the count of cells in a range in vba is Range(“starting cell”,”ending cell”).count
COUNT THE NUMBER OF ROWS IN A RANGE IN VBA
Like the previous , simple count of the selected cell. It is also too easy to find out the number of Rows in a given range. This kind of information is very handy when we are doing some operations with the ranges. SYNTAX TO KNOW THE NUMBER OF ROWS IN A RANCE IN VBA:
Range(“starting cell”,”ending cell”).rows.count
NOTE:REMEMBER ITS ROWS AND NOT ROW. ROW IS ANOTHER PROPERTY. VBA WON’T SAY ANYTHING BUT GIVE RUNTIME ERROR.
COUNT THE NUMBER OF COLUMNS IN A RANGE IN VBA
Like the row count, column count can also be done with the RANGE. It is also too easy to find out the number of columns in the RANGE in VBA. These functions make our task much easier when manipulating the rows or columns. SYNTAX TO KNOW THE NUMBER OF COLUMNS IN A RANGE IN VBA:
Range(“starting cell”,”ending cell”).columns.count
EXAMPLE SHOWING THE USAGE OF COUNT OF SELECTED CELLS OR A SIMPLE RANGE
OBJECTIVE
FIND THE COUNT OF CELLS, ROWS AND COLUMNS IN A SELECTION AND A SIMPLE RANGE. [GIVEN RANGE A2:D20]
CODE
'gyankosh.net
'Various types of counts in Range Objects
Sub countingRange()
Range("A2", "D20").Select
MsgBox ("The number of selected cells are " & Selection.Count)
Dim selectedrowcount As Integer
Dim selectedcolumncount As Integer
selectedrowcount = Selection.Rows.Count
selectedcolumncount = Selection.Columns.Count
MsgBox ("The number of selected cells is " & Selection.Count & _
" and number of rows is " & _
selectedrowcount & " and number of columns is " & selectedcolumncount)
RowCount = Range("A2", "BC3").Rows.Count 'COUNTING THE NUMBER OF ROWS IN A RANGE
columncount = Range("A2", "BC3").Columns.Count 'COUNTING THE NUMBER OF COLUMNS IN A RANGE
cellcount = Range("A2", "BC3").Count 'COUNTING THE CELLS OF A RANGE
MsgBox ("The number of cells is " & RowCount & " and number of rows is " & _
RowCount & " and number of columns is " & columncount)
End SubEXPLANATION
The explanation of the program is simple.The program starts with the declaration of some variables.
Output of all the methods used is an INTEGER.
After taking the output MSGBOX is used to show the result.
RUNNING THE CODE AND OUTPUT
DELETING EMPTY ROWS WITHIN A RANGE USING VBA
OBJECTIVE-DELETE EMPTY ROWS IN A GIVEN RANGE
CREATE A PROCEDURE TO DELETE ALL THE EMPTY ROWS WITHIN THE SELECTED RANGE
PLANNING THE SOLUTION
We’ll find the selected range first.
We can get it by the selection object as we already used this.
After getting the selection we need to find out the rows with no content.
There is a function called COUNTA for this which finds out if row contains data or not.
We need to delete the rows from bottom to first, because the rows will change their relative numbers as the deletion process starts.
We need to take care of that.
CODE
'gyankosh.net
'Deleting empty rows in the selected range
Sub DeleteRowsinRange()
Dim tempRange As Range 'temporary range for the input
Dim firstrow As Integer, LastRow As Integer 'various integers for calculation
Dim r As Integer
Dim Counter As Long
Prompt = "Select a range for the removal of Empty rows."
Title = "Please select a range-Gyankosh.net"
On Error Resume Next ‘Taking input of the range from the user
Set tempRange = Application.InputBox( _
Prompt:=Prompt, _
Title:=Title, _
Default:=ActiveCell.Address, _
Type:=8) ‘Range selection
tempRange.Select ‘Selecting the range
firstrow = Selection.Rows(1).Row
LastRow = Selection.Rows(tempRange.Rows.Count).Row
Application.ScreenUpdating = False 'Always use this method to speed up the macro.
For r = LastRow To firstrow Step -1
If Application.WorksheetFunction.CountA(Rows(r)) = 0 Then
Rows(r).Delete
Counter = Counter + 1
End If
Next r
Application.ScreenUpdating = True
MsgBox Counter & " empty rows were deleted."
End SubEXPLANATION
The codes starts with the declarations of different objects and variables.
We declared a RANGE OBJECT as we’ll be working with the ranges.
We declared a few integer variables for the counting purpose.A few for the loops. The names are self explanatory.
After declaration, we use an INPUT BOX for taking the input from the user. The format of the input box is standard.
Let us get a few things about the INPUT BOX. There are two input boxes, one of the Excel and one of the VBA.
We are discussing one of the Excel right now.
SYNTAX OF INPUT BOX [EXCEL]
Syntax
InputBox (Prompt, Title, Default, Left, Top, HelpFile, HelpContextID, Type)
Parameters
| Name | Required/Optional | Data type | Description |
|---|---|---|---|
| Prompt | Required | String | The message to be displayed in the dialog box. This can be a string, a number, a date, or a Boolean value (Microsoft Excel automatically coerces the value to a String before it is displayed). Maximum length is 255 characters, otherwise there is no prompt, and Application’s method immediately returns Error 2015. |
| Title | Optional | Variant | The title for the input box. If this argument is omitted, the default title is Input. |
| Default | Optional | Variant | Specifies a value that will appear in the text box when the dialog box is initially displayed. If this argument is omitted, the text box is left empty. This value can be a Range object. |
| Left | Optional | Variant | Specifies an x position for the dialog box in relation to the upper-left corner of the screen, in points. |
| Top | Optional | Variant | Specifies a y position for the dialog box in relation to the upper-left corner of the screen, in points. |
| HelpFile | Optional | Variant | The name of the Help file for this input box. If the HelpFile and HelpContextID arguments are present, a Help button will appear in the dialog box. |
| HelpContextID | Optional | Variant | The context ID number of the Help topic in HelpFile. |
| Type | Optional | Variant | Specifies the return data type. If this argument is omitted, the dialog box returns text. |
The following table lists the values that can be passed in the Type argument. Can be one or a sum of the values. For example, for an input box that can accept both text and numbers, set Type to 1 + 2.
| Value | Description |
|---|---|
| 0 | A formula |
| 1 | A number |
| 2 | Text (a string) |
| 4 | A logical value (True or False) |
| 8 | A cell reference, as a Range object |
| 16 | An error value, such as #N/A |
| 64 | An array of values |
After the input, the range has been selected.
We find out the row number of first row in the selection and last row in the selection.
We apply the loop and find out if the last row is empty or not using the function COUNTA.
COUNTA FUNCTION TELLS THE STATUS IF THE ROW IS HAVING ANY CONTENT OR NOT.
If the row is not having any data, the row is deleted.
A counter is also put to count the number of rows deleted.
The objective is achieved.
*We use reverse loop because otherwise it’ll cause us problems when the first row is deleted, the others would shift upwards.
RUNNING THE CODE AND OUTPUT
EXPLANATION
We run the program.
The programs asks us to select the range.
After the range is selected, the program checks for the empty rows and deletes them.
A dialog box tells about the number of rows deleted.
DELETING EMPTY ROWS WITHIN A RANGE USING VBA
OBJECTIVE-DELETE EMPTY ROWS IN A GIVEN RANGE
CREATE A PROCEDURE TO DELETE ALL THE EMPTY ROWS WITHIN THE SELECTED RANGE
PLANNING THE SOLUTION
We’ll find the selected range first.
We can get it by the selection object as we already used this.
After getting the selection we need to find out the rows with no content.
There is a function called COUNTA for this which finds out if row contains data or not.
We need to delete the rows from bottom to first, because the rows will change their relative numbers as the deletion process starts.
We need to take care of that.
CODE
'gyankosh.net
'Deleting empty rows in the selected range
Sub DeleteRowsinRange()
Dim tempRange As Range 'temporary range for the input
Dim firstrow As Integer, LastRow As Integer 'various integers for calculation
Dim r As Integer
Dim Counter As Long
Prompt = "Select a range for the removal of Empty rows."
Title = "Please select a range"
On Error Resume Next 'Taking input of the range from the user
Set tempRange = Application.InputBox( _
Prompt:=Prompt, _
Title:=Title, _
Default:=ActiveCell.Address, _
Type:=8) 'Range selection
tempRange.Select 'Selecting the range
firstrow = Selection.Rows(1).Row
LastRow = Selection.Rows(tempRange.Rows.Count).Row
Application.ScreenUpdating = False 'Always use this method to speed up the macro.
For r = LastRow To firstrow Step -1
If Application.WorksheetFunction.CountA(Rows(r)) = 0 Then
Rows(r).Delete
Counter = Counter + 1
End If
Next r
Application.ScreenUpdating = True
MsgBox Counter & " empty rows were deleted."
End SubEXPLANATION
The codes starts with the declarations of different objects and variables.
We declared a RANGE OBJECT as we’ll be working with the ranges.
We declared a few integer variables for the counting purpose.A few for the loops. The names are self explanatory.
After declaration, we use an INPUT BOX for taking the input from the user. The format of the input box is standard.
Let us get a few things about the INPUT BOX. There are two input boxes, one of the Excel and one of the VBA. We are discussing one of the Excel right now.
SYNTAX OF INPUT BOX [EXCEL]
Syntax
InputBox (Prompt, Title, Default, Left, Top, HelpFile, HelpContextID, Type)
Parameters
| Name | Required/Optional | Data type | Description |
|---|---|---|---|
| Prompt | Required | String | The message to be displayed in the dialog box. This can be a string, a number, a date, or a Boolean value (Microsoft Excel automatically coerces the value to a String before it is displayed). Maximum length is 255 characters, otherwise there is no prompt, and Application’s method immediately returns Error 2015. |
| Title | Optional | Variant | The title for the input box. If this argument is omitted, the default title is Input. |
| Default | Optional | Variant | Specifies a value that will appear in the text box when the dialog box is initially displayed. If this argument is omitted, the text box is left empty. This value can be a Range object. |
| Left | Optional | Variant | Specifies an x position for the dialog box in relation to the upper-left corner of the screen, in points. |
| Top | Optional | Variant | Specifies a y position for the dialog box in relation to the upper-left corner of the screen, in points. |
| HelpFile | Optional | Variant | The name of the Help file for this input box. If the HelpFile and HelpContextID arguments are present, a Help button will appear in the dialog box. |
| HelpContextID | Optional | Variant | The context ID number of the Help topic in HelpFile. |
| Type | Optional | Variant | Specifies the return data type. If this argument is omitted, the dialog box returns text. |
The following table lists the values that can be passed in the Type argument. Can be one or a sum of the values. For example, for an input box that can accept both text and numbers, set Type to 1 + 2.
| Value | Description |
|---|---|
| 0 | A formula |
| 1 | A number |
| 2 | Text (a string) |
| 4 | A logical value (True or False) |
| 8 | A cell reference, as a Range object |
| 16 | An error value, such as #N/A |
| 64 | An array of values |
After the input, the range has been selected.We find out the row number of first row in the selection and last row in the selection.
We apply the loop and find out if the last row is empty or not using the function COUNTA.
COUNTA FUNCTION TELLS THE STATUS IF THE ROW IS HAVING ANY CONTENT OR NOT.
If the row is not having any data, the row is deleted.
A counter is also put to count the number of rows deleted.The objective is achieved.
*We use reverse loop because otherwise it’ll cause us problems when the first row is deleted, the others would shift upwards.
RUNNING THE CODE AND OUTPUT
EXPLANATION
We run the program.
The programs asks us to select the range.
After the range is selected, the program checks for the empty rows and deletes them.
A dialog box tells about the number of rows deleted.