CODEWITHSUNDEEP
vbaexcelmultipleselection
Joel Kanerva
Joel Kanerva

Reputation: 43

Excel VBA: How to select cells using rows and columns

I would like to select the range of cells using rows and columns information. The idea is to select entire column and entire row and then make selection of the overlapping areas. Is it possible to do this in Excel VBA? The idea visualized here

Upvotes: 0

Views: 3977

Answers (2)

Rosetta
Rosetta

Reputation: 2725

first make your cross section selection then run the following code

Sub SelectionCrossSection()
    Dim wRi As Range:   Set wRi = Selection
    Dim wR1 As Range:   Set wR1 = wRi.Areas(1)
    Dim wR2 As Range:   Set wR2 = wRi.Areas(2)
    Dim wRo As Range:   Set wRo = Intersect(wR1, wR2)

    wRo.Select
End Sub

Upvotes: 2

Scott Craner
Scott Craner

Reputation: 152475

The easiest way is to use Cells():

Sub jlkj()
Dim ws As Worksheet
Dim StartRow As Long
Dim EndRow As Long
Dim StartClm As Long
Dim EndClm As Long
Dim rng As Range

StartRow = 6
EndRow = 10
StartClm = 5
EndClm = 5

Set ws = Sheets("Sheet1")
With ws
    Set rng = .Range(.Cells(StartRow, StartClm), .Cells(EndRow, EndClm))
End With
Debug.Print rng.Address

End Sub

If you want to use the Column letter, i.e. "E" instead of the number then:

Sub jlkj()
Dim ws As Worksheet
Dim StartRow As Long
Dim EndRow As Long
Dim StartClm As String
Dim EndClm As String
Dim rng As Range

StartRow = 6
EndRow = 10
StartClm = "E"
EndClm = "E"

Set ws = Sheets("Sheet1")
With ws
    Set rng = .Range(.Cells(StartRow, StartClm), .Cells(EndRow, EndClm))
End With
Debug.Print rng.Address

End Sub

Upvotes: 2

Related Questions