: $$Excel-Macros$$ Why is usedrange here returning the entire
spreadsheet?
All for the education. Keep it coming.
Once I have things working I will go back and try to make
the program more efficient and elegant.
The statement below does not work if you remove the .value.
Back to the not finding
10:49 AM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire
spreadsheet?
The msgbox example seems to have an error but with other reading I
think maybe but not certain I got it.
I got the array version of the rounding to work
is usedrange here returning the entire
spreadsheet?
The msgbox example seems to have an error but with other reading I
think maybe but not certain I got it.
I got the array version of the rounding to work. The evaluate version
is slightly faster but the array version
way faster than
]
On Behalf Of Domain Admin
Sent: Wednesday, April 11, 2012 10:49 AM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire
spreadsheet?
The msgbox example seems to have an error but with other reading I
think maybe but not certain I got
...@gmail.com
-Original Message-
From: Domain Admin
Sent: Sunday, April 15, 2012 6:05 AM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire
spreadsheet?
Well on the read side we definitely match except he makes the point of
saying he
-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Domain Admin
Sent: Wednesday, April 11, 2012 10:49 AM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire
spreadsheet?
The msgbox example seems to have an error
, 2012 4:05 AM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire
spreadsheet?
Well on the read side we definitely match except he makes the point of
saying he is not using SET.
Why is that? What is the difference?
It does indicate
1:18 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire
spreadsheet?
Hence my confusion. Here is the code I am currently using. Sounds
like you are saying the SET should
cause it to fail.
But it only works if I use the SET
[mailto:excel-macros@googlegroups.com]
On Behalf Of Domain Admin
Sent: Sunday, April 15, 2012 4:05 AM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire
spreadsheet?
Well on the read side we definitely match except he makes the point
[mailto:excel-macros@googlegroups.com]
On Behalf Of Domain Admin
Sent: Sunday, April 15, 2012 4:05 AM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire
spreadsheet?
Well on the read side we definitely match except he makes
7:49 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire
spreadsheet?
yeah all the rest was there. Still confused though. He supposedly
does without SET what I cannot do unless I use SET.
On Sun, Apr 15, 2012 at 7:25 PM, Asa Rossoff
: Saturday, April 07, 2012 10:14 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire
spreadsheet?
When you did not use the intersection was the error unable to find the
match function?
In any case
contangoindex = 1
Do While Sheets
[mailto:a...@lovetour.info]
Sent: Monday, April 09, 2012 2:35 AM
To: 'excel-macros@googlegroups.com'
Subject: RE: $$Excel-Macros$$ Why is usedrange here returning the entire
spreadsheet?
This works. Is there a reason why it is not better?
Yes. It won't always work. RANGE.Columns is relative
was to limit
the size of the range properly.
-Original Message-
From: Asa Rossoff [mailto:a...@lovetour.info]
Sent: Monday, April 09, 2012 2:35 AM
To: 'excel-macros@googlegroups.com'
Subject: RE: $$Excel-Macros$$ Why is usedrange here returning the entire
spreadsheet?
This works
@googlegroups.com]
On Behalf Of Domain Admin
Sent: Saturday, April 07, 2012 10:14 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire
spreadsheet?
When you did not use the intersection was the error unable to find the
match function?
In any
Sent: Sunday, April 08, 2012 1:36 AM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire
spreadsheet?
This works. Is there a reason why it is not better?
I did your array replacement but I changed this
Set InputRange
Message-
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Domain Admin
Sent: Monday, April 09, 2012 1:03 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire
spreadsheet?
If this is the answer to my last
[mailto:a...@lovetour.info]
Sent: Monday, April 09, 2012 1:19 PM
To: 'excel-macros@googlegroups.com'
Subject: RE: $$Excel-Macros$$ Why is usedrange here returning the entire
spreadsheet?
Ok but if I use Set BarDate = ... where BarDate is a range
object and I do this inside a procedure, but BarDate
PM
To: 'excel-macros@googlegroups.com'
Subject: RE: $$Excel-Macros$$ Why is usedrange here returning the entire
spreadsheet?
Ok but if I use Set BarDate = ... where BarDate is a range
object and I do this inside a procedure, but BarDate is defined
at the modulelevel, then is BarDate the range
07, 2012 10:14 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire
spreadsheet?
When you did not use the intersection was the error unable to find the
match function?
In any case
contangoindex = 1
Do While Sheets(RawData
you should highlight the entire rows (and same for extra columns),
right-click, delete
the delete key won't cut it (clears the text and formulas but leaves
formats)
or I select those rows/columns, then Alt-E,D
if selecting some cells but not entire row/column, you can delete entire row
with
Did you delete rows using the row indicator at the left of the sheet?
Did you SAVE the file after so doing?
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguille...@gmail.com
From: tangledweb
Sent: Saturday, April 07, 2012 4:24 PM
To: excel-macros@googlegroups.com
Subject:
Ok then I can not use this method at all becaues the columns have
formats set for the entire column (really not efficient for that to be
part of userange).
And I am guessing this statement that I thought was efficient is
actually clearing the entire spreadsheet and not jus the part with
data
Yes and no, but if formats count as part of usedrange then this method
will not work as the formats need to stay in the entire columns which
will have different numbers of rows filled at different times.
On Sat, Apr 7, 2012 at 2:34 PM, dguillett1 dguille...@gmail.com wrote:
Did you delete rows
It would seem that delete does remove the format, But when I delete
all extra rows, all extra columns, and save the file, I still get the
same 99 answer.
On Sat, Apr 7, 2012 at 2:42 PM, Domain Admin domainqu...@gmail.com wrote:
Yes and no, but if formats count as part of usedrange then this
Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire
spreadsheet?
Ok then I can not use this method at all becaues the columns have
formats set for the entire column (really not efficient for that to be
part of userange).
And I am guessing this statement that I thought
-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Domain Admin
Sent: Saturday, April 07, 2012 2:40 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire
spreadsheet?
Ok then I can not use this method at all becaues
]
On Behalf Of Domain Admin
Sent: Saturday, April 07, 2012 2:40 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire
spreadsheet?
Ok then I can not use this method at all becaues the columns have
formats set for the entire column (really
: Saturday, April 07, 2012 3:14 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire
spreadsheet?
I figured the extra row was cleared but that is not a problem.
It seems on testing that clearcontents is working fine leaving the
formats alone
Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire
spreadsheet?
Ok then I can not use this method at all becaues the columns have
formats set for the entire column (really not efficient for that to be
part of userange).
And I am guessing this statement that I thought
@googlegroups.com]
On Behalf Of Domain Admin
Sent: Saturday, April 07, 2012 3:26 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire
spreadsheet?
I can use a loop to find the end, but why is usedrange returning all
rows? That seems to be a fundamental
@googlegroups.com]
On Behalf Of Domain Admin
Sent: Saturday, April 07, 2012 3:14 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire
spreadsheet?
I figured the extra row was cleared but that is not a problem.
It seems on testing
Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire
spreadsheet?
I can use a loop to find the end, but why is usedrange returning all
rows? That seems to be a fundamental issue.
On Sat, Apr 7, 2012 at 3:13 PM, Asa Rossoff a...@lovetour.info wrote:
p.s. other methods
@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Domain Admin
Sent: Saturday, April 07, 2012 3:32 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire
spreadsheet?
No not a range object, just a reference. But here is the entire
, April 07, 2012 5:00 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire
spreadsheet?
using Debug.Print ActiveSheet.Cells.Address returns $1:$1048576
which makes me think is is the true number of rows but
would seem to address only 1
Message-
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Domain Admin
Sent: Saturday, April 07, 2012 5:00 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire
spreadsheet?
using Debug.Print
:00 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire
spreadsheet?
using Debug.Print ActiveSheet.Cells.Address returns $1:$1048576
which makes me think is is the true number of rows but
would seem to address only 1 column so I guess
@googlegroups.com]
On Behalf Of Domain Admin
Sent: Saturday, April 07, 2012 6:29 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire
spreadsheet?
I notice in all the other match functions they are inside a
With Sheets(somesheet)
so I tried putting
-Original Message-
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Domain Admin
Sent: Saturday, April 07, 2012 6:29 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire
spreadsheet?
I notice in all
.
Asa
-Original Message-
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Domain Admin
Sent: Saturday, April 07, 2012 6:29 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire
spreadsheet?
I
, April 07, 2012 5:18 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire
spreadsheet?
I read that after I posted. But doesn't that mean
Sheets(Results).Cells.Resize(-1).Offset(1).Delete
that you suggested would be deleting ever row
@googlegroups.com]
On Behalf Of Domain Admin
Sent: Saturday, April 07, 2012 4:51 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire
spreadsheet?
Writing that book should be easy. Just collect all your question and
answer thread and put them
-
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Domain Admin
Sent: Saturday, April 07, 2012 8:15 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire
spreadsheet?
Thanks for that. I will check
,
Asa
-Original Message-
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Asa Rossoff
Sent: Saturday, April 07, 2012 4:19 PM
To: excel-macros@googlegroups.com
Subject: RE: $$Excel-Macros$$ Why is usedrange here returning the entire
spreadsheet
[mailto:excel-macros@googlegroups.com]
On Behalf Of Domain Admin
Sent: Saturday, April 07, 2012 8:57 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire
spreadsheet?
I tried what you said. Is it trying to make an array out of a range,
or just
-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Domain Admin
Sent: Saturday, April 07, 2012 3:14 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire
spreadsheet?
I figured the extra row was cleared
07, 2012 8:57 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire
spreadsheet?
I tried what you said. Is it trying to make an array out of a range,
or just restrict the range (looks more like the latter). Still got
the error Unable
-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire
spreadsheet?
This is interesting. It would seem to allow for the possibility of
filling in my entire results sheet as an array then dumping it to the
sheet all at once.
But probably not since
@googlegroups.com
Subject: RE: $$Excel-Macros$$ Why is usedrange here returning the entire
spreadsheet?
The term reference in Excel, when referring to a cell or range, means the
textual description of that cell or range (the Address property of a range
object returns the range reference). In VBA
-Original Message-
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Domain Admin
Sent: Saturday, April 07, 2012 9:31 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire
spreadsheet?
That gave me
@googlegroups.com]
On Behalf Of Domain Admin
Sent: Saturday, April 07, 2012 9:31 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire
spreadsheet?
That gave me an idea but it did not work. I thought maybe it could
not find the match
@googlegroups.com
Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire
spreadsheet?
This is interesting. It would seem to allow for the possibility of
filling in my entire results sheet as an array then dumping it to the
sheet all at once.
But probably not since there is no apriori
Now seriously take a break.
You're right :)
However, no, others often know things that stump me. And my knowledge is
superficial in some areas, and I make assumptions..
ttyl :)
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread
@googlegroups.com
Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire
spreadsheet?
That gave me an idea but it did not work. I thought maybe it could
not find the match function because that meant it wanted one where the
second variable was a range instead of an array so
: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Domain Admin
Sent: Saturday, April 07, 2012 10:14 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire
spreadsheet?
When you did not use the intersection
10:14 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire
spreadsheet?
When you did not use the intersection was the error unable to find the
match function?
In any case
contangoindex = 1
Do While Sheets(RawData).Cells(2
56 matches
Mail list logo
