PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES

CONTENTS

RANGES IN VBA-II

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 Sub

EXPLANATION

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

COUNT FUNCTIONS IN SELECTED CELLS AND RANGE
COUNTING CELLS, ROWS AND COLUMNS IN SELECTION AND RANGE

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 Sub

EXPLANATION

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

NameRequired/OptionalData typeDescription
PromptRequiredStringThe 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.
TitleOptionalVariantThe title for the input box. If this argument is omitted, the default title is Input.
DefaultOptionalVariantSpecifies 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.
LeftOptionalVariantSpecifies an x position for the dialog box in relation to the upper-left corner of the screen, in points.
TopOptionalVariantSpecifies a y position for the dialog box in relation to the upper-left corner of the screen, in points.
HelpFileOptionalVariantThe 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.
HelpContextIDOptionalVariantThe context ID number of the Help topic in HelpFile.
TypeOptionalVariant

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.

ValueDescription
0A formula
1A number
2Text (a string)
4A logical value (True or False)
8A cell reference, as a Range object
16An error value, such as #N/A
64An 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

DELETING THE EMPTY ROWS IN A GIVEN RANGE
VBA:DELETING THE EMPTY ROWS IN A GIVEN RANGE

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 Sub

EXPLANATION

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

NameRequired/OptionalData typeDescription
PromptRequiredStringThe 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.
TitleOptionalVariantThe title for the input box. If this argument is omitted, the default title is Input.
DefaultOptionalVariantSpecifies 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.
LeftOptionalVariantSpecifies an x position for the dialog box in relation to the upper-left corner of the screen, in points.
TopOptionalVariantSpecifies a y position for the dialog box in relation to the upper-left corner of the screen, in points.
HelpFileOptionalVariantThe 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.
HelpContextIDOptionalVariantThe context ID number of the Help topic in HelpFile.
TypeOptionalVariant

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.

ValueDescription
0A formula
1A number
2Text (a string)
4A logical value (True or False)
8A cell reference, as a Range object
16An error value, such as #N/A
64An 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

DELETING THE EMPTY ROWS IN A GIVEN RANGE
VBA:DELETING THE EMPTY ROWS IN A GIVEN RANGE

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.

 

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

PREVIOUS ARTICLE
NEXT ARTICLE

LEARN

Encourage us by following at …

Facebook
Twitter
Pinterest

If you have any queries or suggestions or any problem which you would like us to solve for you, kindly mail at

[email protected]