Dim rng As Range
Dim rngSelect As Range
Set rngSelect = Selection
For Each rng In rngSelect.Rows
Selection.Offset(2, 0) = rng.EntireRow.Select
'need to add each modified range to selection
Next rng
I want all modified ranges to be selected in code
excel vba
Juller
It's probably easier and quicker to work with range objects, not the selection. Range supports Union and Intersect operators.
Note: you will only be able to copy the selection if the same columns of each selected row are selected.
"What I'm trying to do is take different selected rows, remove the first two columns from the selection set but leave the rest selected"
Sub DeselectFirstTwoColumns()
Dim rOrigSelection As Range
Dim rNewSelection As Range
Dim rRows As Range
Dim rColumns As Range
Dim rFirstRowSel As Range
Dim iColumn As Long
Dim iCounter As Long
Set rOrigSelection = Selection
Set rRows = rOrigSelection.EntireRow
iCounter = 0
For iColumn = 1 To ActiveSheet.Columns.Count
If Not Intersect(ActiveSheet.Columns(iColumn), rOrigSelection) Is Nothing Then
iCounter = iCounter + 1
If iCounter = 3 Then
Set rColumns = ActiveSheet.Columns(iColumn)
ElseIf iCounter > 3 Then
Set rColumns = Union(ActiveSheet.Columns(iColumn), rColumns)
End If
End If
Next
If iCounter > 2 Then
Set rNewSelection = Intersect(rColumns, rRows)
rNewSelection.Select
End If
End Sub
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
AlekzG
How about building an array of range addresses as you go through the For Each - Next Loop
Or you could build a string that has the addresses, and then use that string to re-select the ranges modified.
Also you do not really need to select a range to work with it, use a With End With construct.
Example:
With Selection.Offset(2,0).rng.EntrireRow
'Do you thing here
End With
Hope this helps
Wassim
Rajiv Gnana
OK try and figure this out, and see if you can use it for your needs:
Sub OnlyHighlighedRange()
Dim rRangeToWork As Range '/This is the range to work with.
Dim rRangeHighlighted As Range '/This is the range Highlighted.
Dim vCell As Variant '/This is the cell mover.
Dim sRangeHighlighted As String '/String to hold the addresses of the selected cells.
Dim lSum As Long '/This is the Sum
Set rRangeToWork = ActiveSheet.Range("A1:A30") '/Example, you could use any range.
Set rRangeHighlighted = Selection.Cells '/This is a ref. to the current selection
For Each vCell In rRangeHighlighted.Cells
sRangeHighlighted = vCell.Address & ", " & sRangeHighlighted
Next vCell
lSum = 0
For Each vCell In rRangeToWork.Cells
If InStr(1, sRangeHighlighted, vCell.Address) > 0 Then '/Cell is highlighted
'/Skip working with this cell
Else
'/Work with this cell.
End If
Next vCell
End Sub
Hope this helps.
Wassim
Egay
HYDROPOWER
Deepak Manchanda