Excel VBA select mulptiple cells based on selection

Question

I would like to select multiple cells based on selection.

And here is my code:

Private Sub CommandButton1_Click()

Selection.EntireRow.Select

End Sub

I want to select the first four columns of rows in multiple cells, instead of the whole rows. How to achieve it?

this is my Excel worksheet

I think you can try `RANGE(A:D).Select`. Reference is here https://learn.microsoft.com/en-us/office/vba/api/excel.range.select — Fnaxiom, Oct 25 '20 at 10:42
You can use a combination of [Range](https://learn.microsoft.com/en-us/office/vba/api/excel.range(object)) and [Cells](https://learn.microsoft.com/en-us/office/vba/api/excel.worksheet.cells). If you want to pick some particular range dynamically, you can use an [If-Then-Else](https://learn.microsoft.com/en-us/dotnet/visual-basic/language-reference/statements/if-then-else-statement) statement perhaps nested in a [For-Next](https://learn.microsoft.com/it-it/dotnet/visual-basic/language-reference/statements/for-next-statement) cycle or [...] — Evil Blue Monkey, Oct 25 '20 at 10:43
[...] [For Each-Next](https://learn.microsoft.com/it-it/dotnet/visual-basic/language-reference/statements/for-each-next-statement) cycle and check every [Range.Value](https://learn.microsoft.com/en-us/office/vba/api/excel.range.value) property of every given cell. Try to avoid using Select (unless you objective is to actually select something, of course). Another property you might be interested in is [Resize](https://learn.microsoft.com/en-us/office/vba/api/excel.range.resize). `Selection.Resize(1,4).Select` might be what you are actually looking for. — Evil Blue Monkey, Oct 25 '20 at 10:44

Samsani Hymavathi · Answer 1 · 2020-10-25T15:06:35.290

1

Check this code:

Option Explicit
Sub Rows_Selection()

Dim rng As Range
Dim active_cells_adress, row_no As Variant
Dim final_selection_adress As String
Dim rng1 As String
Dim i As Integer
Selection.EntireRow.Select

Set rng = Selection
rng1 = rng.Address


final_selection_adress = ""
active_cells_adress = Split(rng1, "$")
For i = 2 To UBound(active_cells_adress)
  row_no = Split(active_cells_adress(i), ",")
  final_selection_adress = final_selection_adress + "A" & row_no(0) & ": D" & row_no(0) + ","
  i = i + 1
Next

final_selection_adress = Left(final_selection_adress, Len(final_selection_adress) - 1)
Range(final_selection_adress).Select


End Sub

edited Oct 25 '20 at 15:06

answered Oct 25 '20 at 11:11

Samsani Hymavathi

144
3

If the selected *cell* is `B2`, it will select `B2:D2` instead of `A2:D2`. If the selected *cells* are `B2`, `C4` and `A6` it will select `B2:D2` instead of `A2:D2`, `A4:D4` and `A6:D6`. – VBasic2008 Oct 25 '20 at 12:40
Exactly, what you have said is right. As per the screenshot, I was thinking first cell of the row is selecting. I have changed my answer now – Samsani Hymavathi Oct 25 '20 at 14:38
Wow, not bad. If you would use `Option Explicit` which would force you to dim all variables: `Dim j As String`, `Dim i As Long`, `Dim rng1 As String`. `Dim n As Variant` and use better variable names especially for the strings, it would be great. Anyway, +1. Well done. You could also do `Set rng = Selection.EntireRow` and remove `Selection.EntireRow.Select`. See [here](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) how to avoid `Select`. Of course, you cannot avoid it in the last line. – VBasic2008 Oct 25 '20 at 14:59
Thank you, I have updated the code including your suggestions. – Samsani Hymavathi Oct 25 '20 at 15:07

score 1 · Accepted Answer · answered Oct 25 '20 at 12:01

Select Rows of Non-Contiguous Range

Copy the codes into a standard module e.g. Module1.

In your command button click event use either of the procedure names in the following way:

Private Sub CommandButton1_Click()
    selectRowsOfListObject
End Sub

or

Private Sub CommandButton1_Click()
    selectRowsOfFirstFourColumns
End Sub

The first procedure will select only the rows of the selected cells in the first (structured) table in the ActiveSheet. Any cells outside the data of the table (DataBodyRange) will be ignored.
The second procedure will select all the row ranges of the selected cells in the first four columns, in the first four columns of the ActiveSheet. Any cells selected outside of the first four columns will be ignored
Each or both of the codes can be used with command buttons on any worksheet when they will refer to the worksheet 'containing' the command button.
If you want a command button on another worksheet to always refer to the first, you will rather have to create a reference to the first worksheet:
Instead of
```
Set ws = ActiveSheet
```
use e.g.
```
Set ws = wb.Worksheets("Sheet1")
```
To better understand the differences, you could add another command button for the second code and then test each of them.

The Code

Option Explicit

Sub selectRowsOfListObject()
    Dim wb As Workbook
    Set wb = ThisWorkbook
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Dim tbl As ListObject
    Set tbl = ws.ListObjects(1)
    If Selection.Worksheet.Name = ws.Name Then
        If TypeName(Selection) = "Range" Then
            Dim rng As Range
            Set rng = Intersect(Selection, tbl.DataBodyRange)
            If Not rng Is Nothing Then
                Set rng = Intersect(rng.Rows.EntireRow, tbl.DataBodyRange.Rows)
            End If
            If Not rng Is Nothing Then
                rng.Select
            End If
        End If
    End If
End Sub

Sub selectRowsOfFirstFourColumns()
    Dim wb As Workbook
    Set wb = ThisWorkbook
    Dim ws As Worksheet
    Set ws = ActiveSheet
    If Selection.Worksheet.Name = ws.Name Then
        If TypeName(Selection) = "Range" Then
            Dim rng As Range
            Set rng = Intersect(Selection.Rows.EntireRow, _
                                ws.Columns(1).Resize(, 4))
            If Not rng Is Nothing Then
                rng.Select
            End If
        End If
    End If
End Sub

Excel VBA select mulptiple cells based on selection

2 Answers2

Select Rows of Non-Contiguous Range