Correcting a selected range-CodePudding

I have a sheet where the user selects a non-contigious range of cells (ie E4, F6, G7) which I want to be able to convert into (A4, A6, A7) keeping the row number but changing the column. I want to only return a single value however if they select more than one cell in the same row or worse still select the entire row. I am a little out of practice with my VBA and can't figure this one out

CodePudding user response：

.Offset and .Resize do not work with non-continous ranges. But dou can use some tricks to get that done.

The idea is to convert the user's selection into an entire row selection and intersect that with the range of column A. The result is the intersecting cells in column A with the originally selected rows.

Option Explicit

Public Sub SelectAInSelectedRows()
    Dim UserSelection As Range
    Set UserSelection = Selection.EntireRow  ' make user's selection entire row
    
    Dim SelectARange As Range
    Set SelectARange = Intersect(UserSelection.Parent.Columns("A"), UserSelection)  ' intersect the rows with column A
    
    ' select the resulting range in column A
    SelectARange.Select
End Sub

CodePudding user response：

Put this into a relevant worksheet module.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Not (Intersect(ActiveSheet.Columns(1), Target) Is Nothing) Then Exit Sub
    ActiveSheet.Cells(Target.Row, 1).Value2 = Target.Value2
End Sub

You can also check if the output cell is not empty, then change

CodePudding user response：

Thanks everyone, all 3 suggestions worked as they utilize the intersect command, that was the bit I had forgotten and why I wasn't progressing

wish I could select all suggestions as correct