Get Selected Rows Using VBA Macro

In this lesson I will show you how to get selected rows of an Excel sheet using a macro. As developers we need to create various kinds of macros to fulfil user’s requirements. Sometimes users want to select rows manually in the excel sheet before running the macros. Then macro may need to detect the rows selected by the user before processing the data. For an example user may want to transfer selected rows to another file or sheet. Or combine data of selected rows. Those are a few examples where you need to get the selected rows. Now let’s learn how to get the selected rows using VBA.

Suppose a user has selected a one row like this.

Then we can use this macro below to print the address of the row in the immediate window.

Sub GetSelectedRows()

     Dim SelectedRows As String
     Dim Rng As Range
     Set Rng = Selection
     SelectedRows = Rng.Address
     Debug.Print SelectedRows

End Sub

Here is the result of the above macro.

What if the user selects multiple rows like this. 

 

Then we will get the below result if we run the same macro.

In the above examples, the result was a string. But usually we need to get the starting row and the ending row as numbers. Then only we can use those values in the next steps. So next let’s look at how we can get the starting row and the ending row. We can create two functions to get starting and ending rows. This is the function to get the starting row.

Function StartingRow() As Long

     Dim SelectedRows As String
     Dim Rng As Range
     Set Rng = Selection
     SelectedRows = Rng.Address
     StartingRow = Replace(Split(SelectedRows, ":")(0), "$", "")

End Function

In this function we have used two inbuilt functions. Split function and Replace function. Split function divides the string at the character “:”. For the above example, Split(SelectedRows, ":")(0) will return $3
Then the replace function will remove the $ symbol.

And this is the function to get the ending row.

Function EndingRow() As Long

     Dim SelectedRows As String
     Dim Rng As Range
     Set Rng = Selection
     SelectedRows = Rng.Address
     EndingRow = Replace(Split(SelectedRows, ":")(1), "$", "")

End Function

Only difference is you have to replace 0 in the split function with 1. Then the split function will split the string at character : and take the second element. Now you can use the above functions in a subroutine.

Sub Main()

     Dim StartRow As Long
     Dim EndRow As Long
     StartRow = StartingRow()
     EndRow = EndingRow()
     Debug.Print "Start - "; StartRow
     Debug.Print "End - "; EndRow

End Sub

Here is the result you will get.

But sometimes users may select non contiguous rows like this. 

Then our first macro (GetSelectedRows) will output this result.

But then our two functions will fail to output correct results. In a next lesson I will show you how to detect a non contiguous range and output the result as a two dimensional array.