how to subtract a range from another?
 

say i wanna set a range of an entire column "minus" first 4 rows. How to do
this in VBA?

how to subtract a range from another?
 

One way:

Dim rng As Range
Set rng = Range(Cells(5, 1), Cells(Rows.Count, 1))
MsgBox rng.Address

Hope this helps
Rowan

serdar wrote:
say i wanna set a range of an entire column "minus" first 4 rows. How to do
this in VBA?

how to subtract a range from another?
 

thats ok.when i use this range as:

rng.Find(x, LookIn:=xlValues)

all 65536 cell are searched? or just searches until the last used/formatted?


"Rowan Drummond" , haber iletisinde sunlari
...
One way:

Dim c As Range
Set rng = Range(Cells(5, 1), Cells(Rows.Count, 1))
MsgBox rng.Address

Hope this helps
Rowan

serdar wrote:
say i wanna set a range of an entire column "minus" first 4 rows. How to
do this in VBA?

how to subtract a range from another?
 

I guess it would search the whole range. An alternative would be:

Dim eRow As Long
Dim rng As Range
eRow = Cells(Rows.Count, 1).End(xlUp).Row
If eRow 4 Then
Set rng = Range(Cells(5, 1), Cells(eRow, 1))
MsgBox rng.Address
End If

Regards
Rowan

serdar wrote:
thats ok.when i use this range as:

rng.Find(x, LookIn:=xlValues)

all 65536 cell are searched? or just searches until the last used/formatted?


"Rowan Drummond" , haber iletisinde sunlari
...

One way:

Dim c As Range
Set rng = Range(Cells(5, 1), Cells(Rows.Count, 1))
MsgBox rng.Address

Hope this helps
Rowan

serdar wrote:

say i wanna set a range of an entire column "minus" first 4 rows. How to
do this in VBA?

how to subtract a range from another?
 

Just to the last used/formatted.

--
Regards,
Tom Ogilvy

"serdar" wrote in message
...
thats ok.when i use this range as:

rng.Find(x, LookIn:=xlValues)

all 65536 cell are searched? or just searches until the last
used/formatted?


"Rowan Drummond" , haber iletisinde sunlari
...
One way:

Dim c As Range
Set rng = Range(Cells(5, 1), Cells(Rows.Count, 1))
MsgBox rng.Address

Hope this helps
Rowan

serdar wrote:
say i wanna set a range of an entire column "minus" first 4 rows. How
to
do this in VBA?

how to subtract a range from another?
 

Actually it just searches until the first value is found, but would only
search in the specified range.

Rowan has shown additional code to ensure there is data below row 4.

--
Regards,
Tom Ogilvy


"serdar" wrote in message
...
thats ok.when i use this range as:

rng.Find(x, LookIn:=xlValues)

all 65536 cell are searched? or just searches until the last
used/formatted?


"Rowan Drummond" , haber iletisinde sunlari
...
One way:

Dim c As Range
Set rng = Range(Cells(5, 1), Cells(Rows.Count, 1))
MsgBox rng.Address

Hope this helps
Rowan

serdar wrote:
say i wanna set a range of an entire column "minus" first 4 rows. How
to
do this in VBA?

how to subtract a range from another?
 

another way to protect for blanks below row 4 is

set rng = Range(cells(1,1),cells(rows.count,1).End(xlup))
if rng.count <= 4 then
set rng = Range("A5")
else
set rng = rng.offset(4,0).Resize(rng.count-4,1)
end if

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
Actually it just searches until the first value is found, but would only
search in the specified range.

Rowan has shown additional code to ensure there is data below row 4.

--
Regards,
Tom Ogilvy


"serdar" wrote in message
...
thats ok.when i use this range as:

rng.Find(x, LookIn:=xlValues)

all 65536 cell are searched? or just searches until the last
used/formatted?


"Rowan Drummond" , haber iletisinde sunlari
...
One way:

Dim c As Range
Set rng = Range(Cells(5, 1), Cells(Rows.Count, 1))
MsgBox rng.Address

Hope this helps
Rowan

serdar wrote:
say i wanna set a range of an entire column "minus" first 4 rows. How
to
do this in VBA?