CODEWITHSUNDEEP
excelvbalistobjectexcel-tables
Adrian
Adrian

Reputation: 15

Excel VBA select mulptiple cells based on selection

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 enter image description here

Upvotes: 1

Views: 149

Answers (2)

Samsani Hymavathi
Samsani Hymavathi

Reputation: 134

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

Upvotes: 1

VBasic2008
VBasic2008

Reputation: 54807

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

Upvotes: 1

Related Questions