Need help programming some variable ranges
 

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?

Need help programming some variable ranges
 

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

"havocdragon" wrote:

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?

Need help programming some variable ranges
 

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


"havocdragon" wrote:

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?

Need help programming some variable ranges
 

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
'<<==============


---
Regards,
Norman



"havocdragon" wrote in message
...
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?

Need help programming some variable ranges
 

Havoc,

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

HTH,
Bernie
MS Excel MVP

"havocdragon" wrote in message
...
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?