Need help programming some variable ranges

[Guest]

Hey all,

I am working with a variable amount of records on an excel file, and each of
them are selected differently, heres my solution however, I am having
difficulties with one peice of code...

ActiveCell.EntireRow.Select
Selection.Insert Shift:=xlDown
ActiveCell.Offset(-1, 0).Select
Range(Selection, Selection.End(xlUp)).Select

Lets say for example this selects A3:A6. What I want to do now is select all
the data in those Rows to the N column, however the goto current region
method wont work because a) there isnt data in every column and b) there is
data after the N column.

I have currently...

range(Selection, Columns("N:N")).select

However this will select everything from A:N.

Any ideas?

 

[Guest]

Sub test()
Dim Rng1 As Range
Dim Rng2 As Range

Set Rng1 = Selection.EntireRow
Set Rng2 = Range("A:N")

Application.Intersect(Rng1, Rng2).Select

End Sub

 

[Guest]

Selection.Resize(, 14).Select
will select the range you are after.

However, You should avoid SELECTING ranges. It's much better to just
describe a range and then use a method or property to do something with it.

Something like:

Dim Rng1 As Range
Dim Rng2 As Range
Dim Rng3 As Range
Dim lRow As Long

lRow = 3

Set Rng1 = ActiveCell
Rng1.EntireRow.Insert Shift:=xlDown
Set Rng2 = Range("A" & lRow, Rng1.End(xlUp))
Set Rng3 = Rng2.Resize(, 14)
Rng3.Interior.ColorIndex = 3

 

[Norman Jones]

Hi HavocDragon,

It is rarely necessary, or desirable to make selections.

Perhaps something like the following will satisfy your requirements:

'==============>>
Public Sub Tester()
Dim rng As Range

With ActiveCell
.EntireRow.Insert Shift:=xlDown
With .EntireRow.Cells(1).Offset(-2, 0)
Set rng = Range(.Item(1), .End(xlUp)).Resize(, 14)
End With
End With

'Now do something with the Rng objec, e.g.:
rng.Interior.ColorIndex = 6


End Sub
'<<==============

 

[Bernie Deitrick]

Havoc,

Intersect(Selection.EntireRow, Range("A:N")).Select

HTH,
Bernie
MS Excel MVP