How to select multiple rows from active cells [SOLVED]

[dronka]

I'd like to create VBA that will look to see which cells are active and then select all of the rows for those cells. E.g. On Sheet 1 I select cells A1, B1, C1. I'd like the macro to then select rows A, B, and C.

 

[NARAYANK991]

Hi ,


I think there is some confusion ; when you select A1 , B1 and C1 , these 3 cells are all on the same row , but in different columns ; do you want that the macro should select the 3 relevant columns ?


When giving an example always choose a generalized example ; thus , suppose the cells you select are C7 , J13 and AB37 ; what rows and / or columns would you like to be selected through the macro ?


Narayan

 

[shrivallabha]

And even then you shouldn't need a macro.


With the cells selected:

If you press CTRL + {SPACEBAR}: it will select columns

If you press SHIFT + {SPACEBAR}: it will select rows

 

[dronka]

Narayan - You are correct. I meant A1, A2, A3 -- three cells in three different rows. It could also be A1, B5, and E99.


So, if I select cells A1, A2, and A3, I'd like the macro to automatically activate the rows for those three cells. From there, the code will copy the rows and paste them into another sheet (which I already know how to do). Thanks!


David

 

[dronka]

shrivallabha - Thank you for these shortcuts. I use them all the time!


David

 

[dronka]

Narayan - You are correct. I meant A1, A2, A3 -- three cells in three different rows. It could also be A1, B5, and E99.


So, if I select cells A1, A2, and A3, I'd like the macro to automatically activate the rows for those three cells. From there, the code will copy the rows and paste them into another sheet (which I already know how to do).


David

 

[NARAYANK991]

Hi David ,


The Selection property returns the selected cells ; so :

[pre]

Selection.EntireRow.Select
will select the entire row , and :

[pre][code]Selection.EntireRow.Copy

[/pre]
will copy the entire row.


If you don't want to select the entire row , then the following procedure will do the job ; you can modify it as required.

Public Sub Multiple_Select()
Dim copy_range As Range, new_range As Range, cell As Range
Dim i As Long, number_of_rows As Long, min_row As Long, max_row As Long
Dim min_col As Integer, max_col As Integer

number_of_rows = 0
min_row = Rows.Count: max_row = 0: min_col = Columns.Count: max_col = 0
For Each cell In Selection
With cell
If min_row > .Row Then min_row = .Row
If max_row < .Row Then max_row = .Row
If min_col > .Column Then min_col = .Column
If max_col < .Column Then max_col = .Column
End With
Next

Set new_range = Range(Cells(min_row, min_col), Cells(max_row, max_col))

For i = 1 To max_row - min_row + 1
If Not (Application.Intersect(Selection, new_range.Rows(i)) Is Nothing) Then
If copy_range Is Nothing Then
Set copy_range = new_range.Rows(i)
Else
Set copy_range = Union(copy_range, new_range.Rows(i))
End If
End If
Next

copy_range.Select
End Sub[/code][/pre]
Narayan

 

[dronka]

Thank you, Narayan! Works like charm (I used the selection.entirerow code).