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
-
Please see how to ask.findwindow– findwindow2016-04-19 16:38:52 +00:00Commented Apr 19, 2016 at 16:38
Add a comment
|
2 Answers
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
Comments
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
2 Comments
Scott Craner
Wow, I understood it completely different but I can see how this is the correct understanding.
Rosetta
@ScottCraner OP's title is vague, I'd have succumbed to that too had i not take enough caffeine today :D