CODEWITHSUNDEEP
excelvba
Michi
Michi

Reputation: 5481

Extract first row numbers of row adress using VBA

I have an empty Excel spreadsheet in which I defined a variable Row in the name manager.
This variable currently refers to 3:3 but can switch any time.
Therefore, it could also be 105:105 or 5000:5000 or any other number possible.

Now I use the following VBA to extract the first numbers of the row adress:

Sub Extract_Row_Adress()
    If Len(Sheet1.Range("Row").Address(RowAbsolute:=False, ColumnAbsolute:=False)) <= 3 Then
        RowNumber = Mid(Sheet1.Range("Row").Address(RowAbsolute:=False, ColumnAbsolute:=False), 1, 1)
    Else
        If Len(Sheet1.Range("Row").Address(RowAbsolute:=False, ColumnAbsolute:=False)) <= 5 Then
            RowNumber = Mid(Sheet1.Range("Row").Address(RowAbsolute:=False, ColumnAbsolute:=False), 1, 2)
        Else
            If Len(Sheet1.Range("Row").Address(RowAbsolute:=False, ColumnAbsolute:=False)) <= 7 Then
                RowNumber = Mid(Sheet1.Range("Row").Address(RowAbsolute:=False, ColumnAbsolute:=False), 1, 3)
            Else
                If Len(Sheet1.Range("Row").Address(RowAbsolute:=False, ColumnAbsolute:=False)) <= 9 Then
                    RowNumber = Mid(Sheet1.Range("Row").Address(RowAbsolute:=False, ColumnAbsolute:=False), 1, 4)
                Else
                End If
            End If
        End If
    End If

    Sheet1.Range("A5").Value = RowNumber
End Sub

All this works fine so far.

As you can see in the VBA I need to use several IF statements because the first numbers of the row adress can vary in their number of digits. According to this I have to adjust the lengths of the Mid function with ( 1, 1) ( 1, 2) ( 1, 3) ( 1, 4) and so on.

I am wondering if there is a better way to do this without having all this If statements running?

Upvotes: 1

Views: 404

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57743

This returns the row number

Sheet1.Range("Row").Row

If Range("Row") is eg "4:6" then 

Sheet1.Range("Row").Row 'returns 4
Sheet1.Range("Row").Row + Sheet1.Range("Row").Rows.Count - 1 'returns 6

Upvotes: 2

Related Questions