RANGES IN VBA-II

Table of Contents

• INTRODUCTION
• COUNT THE NUMBER OF SELECTED CELL IN VBA
• COUNT THE NUMBER OF ROWS IN SELECTED CELL IN VBA
• COUNT THE NUMBER OF COLUMNS IN SELECTED CELL IN VBA
• COUNT THE NUMBER OF CELL IN A RANGE IN VBA
• COUNT THE NUMBER OF ROWS IN A RANGE IN VBA
• COUNT THE NUMBER OF COLUMNS IN A RANGE IN VBA
• EXAMPLE SHOWING THE USAGE OF COUNT OF SELECTED CELLS OR A SIMPLE RANGE
• DELETING EMPTY ROWS WITHIN A RANGE USING VBA
• Syntax
• Parameters
• DELETING EMPTY ROWS WITHIN A RANGE USING 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.

This is the part II of the topic.

Find part-i here.

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

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

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.

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

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.

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.