
PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES
CONTENTS
RANGES IN VBA-II
INTRODUCTION
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
Range(“A2″,”D20”).SELECT
SELECTION.ROWS.COUNT
COUNT THE NUMBER OF COLUMNS IN SELECTED CELL IN VBA
Range(“A2″,”D20”).SELECT
SELECTION.COLUMNS.COUNT
COUNT THE NUMBER OF CELL IN A RANGE IN VBA
COUNT THE NUMBER OF ROWS IN A RANGE 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
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
RUNNING THE CODE AND OUTPUT
DELETING EMPTY ROWS WITHIN A RANGE USING VBA
OBJECTIVE-DELETE EMPTY ROWS IN A GIVEN RANGE
PLANNING THE SOLUTION
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
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 |
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
PLANNING THE SOLUTION
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
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 |
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.
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.