Sometimes you need to know the number of rows and columns of a selected range in the worksheet. In this article, we will introduce two methods for you.
When you select a range in the worksheet and you haven’t released your mouse, you will see the row number and column number of the area in the name box. However, the moment you release your mouse, the number will disappear. It is hard for you to remember the number all the time. What if you need to get the number the next time? Here we have found two methods to solve this problem.
Method 1: Use Shift Key
In this method, you need to use the Shift keys to select the range again.
- In the worksheet, press the button “Shift” on the keyboard and hold it.
- And then press one of the arrow buttons on the keyboard. We will press the right arrow button to make this example. You will see that the range will add a new column.
- After that, press the arrow key with the opposite direction. We will press the left arrow key. Thus, the range is the same range that you have selected.
And at this time, you can see the row number and column number in the name box.
Remember that in the whole process, you need to hold the button “Shift”. When you know the number, you can release it.
Method 2: Use VBA Macro
Here you can also use the VBA macro to know the number of rows and columns.
- Press the button “Alt +F11” on the keyboard.
- And then insert a new module into the Visual Basic editor.
- Now copy the following codes into the module:
Sub CountNumberOfRowsAndColumnsInSelection() MsgBox "The number of rows in the range is" & " " & Selection.Rows.Count & _ Chr(10) & "The number of columns in the range is" & " " & Selection.Columns.Count End Sub
- After that, run this macro. And in the pop-up window, you will get the number.
Compare the Two Methods
The table below shows the advantages and disadvantages of the two methods.
Comparison |
Use Shift Key |
Use VBA Macro |
Advantages |
This method is easy to understand. | You can get the result easily and quickly by using macro. |
Disadvantages |
There are more steps in this method, which will cause you more time to get the answer. | If you are not familiar with Excel VBA, you may meet with problems when you run the macro. |
Both of the two methods are very effective. You can use either of the method in your worksheet.
Excel Safety is Essential
We all know that Excel safety is very essential for every user. In Excel files, there are a lot of mission-critical data and information. Once Excel corrupts, you need to repair it as soon as you can. In order to help you better get back your files and repair damaged xlsx, we have developed a powerful tool.
Author Introduction:
Anna Ma is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair Word doc data corruption and outlook repair software products. For more information visit www.datanumen.com
Leave a Reply