How to select fixed part of rows based on cells selection , Excel vba?

Question

in the below code, if I select cell B* or cell C* , It subsequently selects range B*:G* on the same row. please, How to include non-contiguous cell (E) in Const cFirstRow As String = "B3:C3" .I tried "B3:C3,E3" but it gives me an error "Run-time error '1004': Method 'Range' of object '_Worksheet' failed.

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    Const cFirstRow As String = "B3:C3"
    Const sCols As String = "B:G"
    
    Dim crg As Range
    With Range(cFirstRow)
        Set crg = .Resize(Rows.Count - .Row + 1)
    End With
    
    Dim irg As Range: Set irg = Intersect(crg, Target)
    
    If Not irg Is Nothing Then
    
        Dim srg As Range, arg As Range, rrg As Range
        For Each arg In irg.Areas
            For Each rrg In arg.Rows
                If srg Is Nothing Then
                    Set srg = Columns(sCols).Rows(rrg.Row)
                Else
                    Set srg = Union(srg, Columns(sCols).Rows(rrg.Row))
                End If
            Next rrg
        Next arg
        
        If Not srg Is Nothing Then
            srg.Select
        End If
    
    End If
        
End Sub

So, would yo like to select the range "B:C,D" of the clicked cell row? — FaneDuru
– FaneDuru, Commented Jun 20, 2021 at 18:42
This is what I meant, but I wrongly wrote... I will prepare a piece of code able to select "B:C,E,G". It should be shorter then your code, I think. — FaneDuru
– FaneDuru, Commented Jun 20, 2021 at 18:58

FaneDuru · Accepted Answer · 2021-06-20 20:29:47Z

2

Please, test the next code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Const cFirstRow As String = "B3:C3,E3"
    Const sCols As String = "B:G"
    
    Dim crg As Range
    With Range(cFirstRow)
        Set crg = Intersect(.Areas(1).EntireRow.Resize(rows.Count - .row + 1), .EntireColumn)
    End With
    
    Dim irg As Range: Set irg = Intersect(crg, Target)
    
    If Not irg Is Nothing Then
       'Debug.Print irg.Address: ' Stop
        Dim srg As Range, arg As Range, rrg As Range
        For Each arg In irg.Areas
            For Each rrg In arg.rows
                If srg Is Nothing Then
                    Set srg = Columns(sCols).rows(rrg.row)
                Else
                    Set srg = Union(srg, Columns(sCols).rows(rrg.row))
                End If
            Next rrg
        Next arg
        
        If Not srg Is Nothing Then
            srg.Select
        End If
    
    End If
        
End Sub

edited Jun 20, 2021 at 20:29

answered Jun 20, 2021 at 19:11

FaneDuru

42.3k4 gold badges22 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

13 Comments

Leedo Over a year ago

after click , the selection is "B:C,E,G" , it suppose to be "B:G"

Leedo Over a year ago

addition note: I can not select non-contiguous selection ( previously i was able to ).

FaneDuru Over a year ago

@Waleed_wwm This is simpler... I wrongly understood your question and I thought that you said that in your last comment by mistake. I will adapt the code immediately.

FaneDuru Over a year ago

@Waleed_wwm What do you mean by "not select non-contiguous selection"? Do you intend to select more then a cell? For now, please test the updated code...

Leedo Over a year ago

yes , previously i was able to select non-contiguous cells in different rows

|

Collectives™ on Stack Overflow

How to select fixed part of rows based on cells selection , Excel vba?

1 Answer 1

13 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

13 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related