Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I want to add or subtract a range of cells | Excel Discussion (Misc queries) | |||
trying to subtract a range from a cell | Excel Worksheet Functions | |||
subtract a range from an added range | Excel Discussion (Misc queries) | |||
I need to subtract a range of numbers | Excel Worksheet Functions | |||
How to subtract some rows from a range | Excel Programming |