CODEWITHSUNDEEP
exceldashboardbusiness-objectsxcelsius
user1429595
user1429595

Reputation: 2755

How to select a range diagonally in Excel?

Hi I have a simple question, how to select a range diagonally in Excel, let's say I need to select (a3 and b1) or (a3 and b2) or simply a1 and b2, I need this range for SAP dashboard design (xcelsius) and Holding down a control key was NOT an Option!!

Thanks

Upvotes: 1

Views: 12015

Answers (2)

Ethun_Hunt
Ethun_Hunt

Reputation: 267

I made a userform to select any number of cells in any direction. I don't know use of this.

Option Explicit
'Run this to open Userform named "Select_Diago"
Sub open_form()
Select_Diago.Show
End Sub

This thing will pop up then

enter image description here

Enter the number of cells to select and direction in which it need to be selected (default is set to right down)

Private Sub Submit_Click()
Dim Direction As String
Select_Diago.Hide
Direction = "SE"
If NE.Value = True Then Direction = "NE"
If SE.Value = True Then Direction = "SE"
If SW.Value = True Then Direction = "SW"
If NW.Value = True Then Direction = "NW"
Call Select_Diagonal(Direction, TextBox1.Value - 1)
End Sub

Sub Select_Diagonal(Direction As String, Num As Integer)
Dim Diagonal As Range, i As Integer
Dim StrEx As String
'Num = InputBox("How Many cells would you like to select diagonally?")
Select Case Direction
Case "SW"
'For SW
For i = 0 To Num
    On Error Resume Next
    StrEx = StrEx + (Split(Cells(, ActiveCell.Column - i).Address, "$")(1)) + CStr((ActiveCell.Row + i)) + ","
Next i
Case "SE"
'For SE
For i = 0 To Num
    On Error Resume Next
    StrEx = StrEx + (Split(Cells(, ActiveCell.Column + i).Address, "$")(1)) + CStr((ActiveCell.Row + i)) + ","
Next i
Case "NE"
'For NE
For i = 0 To Num
    On Error Resume Next
    StrEx = StrEx + (Split(Cells(, ActiveCell.Column + i).Address, "$")(1)) + CStr((ActiveCell.Row - i)) + ","
Next i
Case "NW"
'For NW
For i = 0 To Num
    On Error Resume Next
    StrEx = StrEx + (Split(Cells(, ActiveCell.Column - i).Address, "$")(1)) + CStr((ActiveCell.Row - i)) + ","
Next i
End Select
StrEx = Left(StrEx, Len(StrEx) - 1)
ActiveSheet.Range(StrEx).Select
End Sub

Result 1: enter image description here

Result 2: enter image description here

Upvotes: 0

Daniel Moura
Daniel Moura

Reputation: 7966

You can't select a range diagonally. In your situation I think the best option is to get the range you want and copy to a column using a formula.

For example, if you want to select a1, b2 and c3. In cell d1 put =if(a1="";"";a1), in cell d2 =if(b2="";"";b2) and in cell d3 =if(c3="";"";c3). Then you select d1 to d3.

Upvotes: 1

Related Questions