VBA for selecting Specific Cells Range in Selected Rows

[excelakos]

Hi everyone! I wish you all the best for 2021! I need to do this. In a table range i select multiple cells in different rows. In example i have selected cells in rows 2,3,5,7,10 inside the table range.
Now i want a macro to first recognize the rows (of the table) of the selected cells and in each of the rows to sellect for me specific cell ranges in example in each row i want the first till the 10th column plus the 15th till 20th column
This means i want to be selected in row 2 the 1st till 10th column cell of this row plus the 15th till 20th column cell of this row
Same for all the other rows 3,5,7,10 or whatever the rows would be

 

[EXCEL MAX]

Hello Excelakos,
here is code that will help you.
Insert this in the "Sheet1" module and add "CommandButton1" on this sheet.
Press "Ctrl" while selecting multiple cells.
Press command button and cells will be selected.
In the end press command button again and cells will be deselected.

Dim vA()
Dim vN As Integer, vI As Integer
Dim vR As Long
Dim vS As String


Private Sub CommandButton1_Click()
  
    On Error GoTo EX:
    For vI = 1 To UBound(vA)
        vR = Range(vA(vI)).Row
        vS = vS & "A" & vR & ":" & "J" & vR & "," _
                & "O" & vR & ":" & "T" & vR & ","
    Next
    vS = Left(vS, Len(vS) - 1)
    Range(vS).Activate
    vS = ""
    vN = 0
    ReDim Preserve vA(vN)
EX:

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      
    vN = vN + 1
    ReDim Preserve vA(vN)
    vA(vN) = ActiveCell.Address

End Sub

 

[Fluff]

Another option

Sub excelakos()
   Dim Rng As Range, Sel As Range
   
   Set Sel = Range("A" & ActiveCell.Row)
   For Each Rng In Selection.Areas
      Set Sel = Union(Sel, Intersect(Rng.EntireRow, Range("A:J,O:T")))
   Next Rng
   Sel.Select
End Sub

 

[excelakos]

Hello Excelakos,
here is code that will help you.
Insert this in the "Sheet1" module and add "CommandButton1" on this sheet.
Press "Ctrl" while selecting multiple cells.
Press command button and cells will be selected.
In the end press command button again and cells will be deselected.

Dim vA()
Dim vN As Integer, vI As Integer
Dim vR As Long
Dim vS As String


Private Sub CommandButton1_Click()
 
    On Error GoTo EX:
    For vI = 1 To UBound(vA)
        vR = Range(vA(vI)).Row
        vS = vS & "A" & vR & ":" & "J" & vR & "," _
                & "O" & vR & ":" & "T" & vR & ","
    Next
    vS = Left(vS, Len(vS) - 1)
    Range(vS).Activate
    vS = ""
    vN = 0
    ReDim Preserve vA(vN)
EX:

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
     
    vN = vN + 1
    ReDim Preserve vA(vN)
    vA(vN) = ActiveCell.Address

End Sub

Hi Max!! Thank you very much for your time!! I am not sure about which part goes where. The Lines including the Dims should go together with Private Sub CommandButton1_Click() in the sheet's code and then the Private Sub Worksheet_SelectionChange(ByVal Target As Range) should go in a module and assigned to a button in the sheet?

 

[excelakos]

Another option

Sub excelakos()
   Dim Rng As Range, Sel As Range
  
   Set Sel = Range("A" & ActiveCell.Row)
   For Each Rng In Selection.Areas
      Set Sel = Union(Sel, Intersect(Rng.EntireRow, Range("A:J,O:T")))
   Next Rng
   Sel.Select
End Sub

Fluff that was so smooth! Many thanks!!! I changed the ranges the real are different that the ones i used to state my needs and all work like a charm!!!

 

[Fluff]

Glad to help & thanks for the feedback.

 

[EXCEL MAX]

You are right,
Fluff's solution is much more simple.
Amazing...

 

[excelakos]

Glad to help & thanks for the feedback.

May i ask one more little favor. Could you make it work for any single Column too? (Not a union of Columns)

 

[Fluff]

In what way?

 

[excelakos]

In what way?

Exactly the same but to make it select not a union of columns but just a single column. Instead of this
Set Sel = Union(Sel, Intersect(Rng.EntireRow, Range("A:J,O:T"))) to get just a single column lets say column R