Asa! Good to hear from you. I am fine. Hope the same for you. The
example I posted seemed to work but I admit I did not check to see if
the correct day was returned. Tomorrow I will do that and try out
your format function version (which I think someone else posted
earlier but I had not tried
t; From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
> On Behalf Of Domain Admin
> Sent: Monday, April 23, 2012 1:47 PM
> To: excel-macros@googlegroups.com
> Subject: Re: $$Excel-Macros$$ Can a pointer or similar be set to one array
> or another depending on co
gt; -Original Message-
> From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
> On Behalf Of Domain Admin
> Sent: Monday, April 23, 2012 1:08 PM
> To: excel-macros@googlegroups.com
> Subject: Re: $$Excel-Macros$$ Can a pointer or similar be set to on
.
> ' Examine the stock array
> ...
> GetReturnValues = (the values you want to return)
> End Function
>
> Asa
>
> -Original Message-
> From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
> On Behalf Of Domain Admin
> Sent: Mon
"a2") then X = (source1) else X=(source2)
>
> Asa
>
> -Original Message-
> From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
> On Behalf Of Domain Admin
> Sent: Sunday, April 22, 2012 11:16 PM
> To: excel-macros@googlegroups.com
> Subj
Well the pointer idea as described does not work. I have to have both
array1 and array2 so can not choose to create one or other.
I can create a third usedarray which is a copy of the one specified by
the condition so I trade off a lot of wasted data space (not really
a problem but not elegant) vs
ect than ClearContents...
> .UsedRange=Empty means .UsedRange.Value=Empty -- it is only clearing
> values, same as ClearContents. .Delete removes the range from .UsedRange
> entirely.
>
> -Original Message-
> From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
&
Thanks but I can easily clear the entire sheet. What is needed is to
be able to clear a set of columns except not the header row and then
paste in new column data and have usedrange be just the number of rows
with the new data. I will try to see if I can use empty on the
columns.
On Thu, Apr 1
fails trying to remove them.
Thanks.
Asa, you have given me examples of this before. I throw myself upon my sword.
On Thu, Apr 19, 2012 at 12:04 AM, David Grugeon wrote:
> make your second line
>
> For index = 2 To tmprange.Rows.count
>
>
> On 19 April 2012 16:47, Domain Admin w
, _
SearchDirection:=xlNext, LookIn:=LookInConstant).Column)
Set UsedDataRange = WS.Range(FirstCell, LastCell)
End Function
On Wed, Apr 18, 2012 at 6:16 PM, Domain Admin wrote:
> How do you call this?
>
> With Sheets(RawData)
> tmprange = UsedDataRange(, True
a range)
On Wed, Apr 18, 2012 at 5:38 PM, Domain Admin wrote:
> Saving did not help unless you mean I also have to close and re-open
> which is too much.
> I may use that other function.
>
> On Wed, Apr 18, 2012 at 5:32 PM, Cecil wrote:
>> Save the file and then check "
as a range)
On Wed, Apr 18, 2012 at 4:54 PM, Sam Mathai Chacko wrote:
> This might be of interest
>
> http://www.excelfox.com/forum/f22/replacement-flawed-usedrange-property-374/
>
> Regards,
> Sam Mathai Chacko
>
>
> On Thu, Apr 19, 2012 at 5:22 AM, Domain Admin
p; RANGE.CurrentRegion.Address
>
> If that doesn't work, please share how you are importing your data, and the
> output of the debug.print statements.
>
> P.S. You might consider using unique sheets for each table of data so there
> is no data in other columns parrallel t
Saving did not help unless you mean I also have to close and re-open
which is too much.
I may use that other function.
On Wed, Apr 18, 2012 at 5:32 PM, Cecil wrote:
> Save the file and then check "usedRange" You will find it properly updated
> when saved.
>
> Follow the link Sam gave you. I hav
est
>
> http://www.excelfox.com/forum/f22/replacement-flawed-usedrange-property-374/
>
> Regards,
> Sam Mathai Chacko
>
>
> On Thu, Apr 19, 2012 at 5:22 AM, Domain Admin wrote:
>>
>> Actually ever weirder. If I keep clearing the current region and
>> pastin
Actually ever weirder. If I keep clearing the current region and
pasting in the exact same
data over and over the usedrange number of rows increases by 1 each time.
On Wed, Apr 18, 2012 at 4:41 PM, Domain Admin wrote:
> Sorry Don but I can not comply with your request in this case easily.
&g
unded by a completely empty row on top and bottom, and a
> completely empty column on left and right.
>
>
>
> Asa
>
>
>
> -Original Message-
> From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
> On Behalf Of Domain Admin
Deleting the columns from row 2 (save the header row) to xldown and
shifting cells up does not solve it.
On Wed, Apr 18, 2012 at 4:04 PM, Domain Admin wrote:
> Never mind about the ROUND part. It returns 0 for a truly empty cell.
>
> On Wed, Apr 18, 2012 at 4:01 PM, tangledweb wrote
Never mind about the ROUND part. It returns 0 for a truly empty cell.
On Wed, Apr 18, 2012 at 4:01 PM, tangledweb wrote:
> I am doing many tests on columns of date pasted into a sheet. Sometimes
> more rows, sometimes less.
> It is important that I be able to define the working range as just wh
ry x iterations through the loop if any slowdown concern.)**
> **
>
> ** **
>
> Asa
>
> ** **
>
> *From:* excel-macros@googlegroups.com [mailto:
> excel-macros@googlegroups.com] *On Behalf Of *Domain Admin
> *Sent:* Tuesday, April 17, 2012 12:15 AM
>
> *To:*
icolon simply leaves cursor in-place).
>
> ** **
>
> Asa
>
> ** **
>
> *From:* excel-macros@googlegroups.com [mailto:
> excel-macros@googlegroups.com] *On Behalf Of *Domain Admin
> *Sent:* Monday, April 16, 2012 11:46 PM
> *To:* excel-macros@googlegroups.com
&g
Everything for the watch set up exactly as you describe and the watch
window shows exactly what you said below.
But it does not break. Program runs to the end though maxrow only shows
111 even though it goes beyond that.
I will figure it out.
Range.show works great though as you say way faster th
ge.Value2
>
> is equivalent to:
> With Sheets(RawData)
> DataArray = .Range(.Cells(2, BarOpen), .Cells(.UsedRange.Rows.Count,
> EContango)).Value2
> End With
> ?
>
> -Original Message-
> From: excel-macros@googlegroups.com [mailto:excel-macros@googlegrou
dRange.Rows.Count, EContango))
>
> End With
>
> ' Second, let's copy everything we need into an array for efficiency
>
> DataArray = tmprange.Value2
>
>
>
> Asa
>
>
>
> -Original Message-
> From: excel-macros@googlegroups.com
array of values, thus Set wouldn't work. -- Set is for
> objects only.
>
> Agreed, don't worry if you're satisfied with the performance. You can
> always massage things later, at your leisure, if desired.
>
> Asa
>
> -Original Message-
> From:
ur file direct to me if desired with a complete explanation.
>
>
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguille...@gmail.com
> -----Original Message- From: Domain Admin
> Sent: Sunday, April 15, 2012 6:05 AM
>
> To: excel-macros@googlegroups.com
&
---
> From: excel-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?
&
erations on a range without a
> VBA loop, but it's a good article with straightforward examples.
>
>
>
> Asa
>
>
>
> -Original Message-
> From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
> On Behalf Of Domain Admin
> Sent: We
Never mind. Too late to be trying to debug code. Left out the actual
assignment FillResults = x about
which VBA is apparently quite picky.
On Sun, Apr 15, 2012 at 2:05 AM, tangledweb wrote:
> I assume this has to do with the assignment being to a variable that is
> passed into the function, bu
Ramping up my ignorance to the next level I guess.
I only have one workbook and it is active so is the workbook part not needed?
Changing to this worked but I thought that issue was resolved last
night by the removal of the
period in front of cells. But that means the cells associated with
the ac
Thanks Rajan but that would not have made a difference. I already had
this definition I did not show
Const RawData As String = "RawData"
I should have mentioned that I suppose.
On Sat, Apr 14, 2012 at 12:25 AM, Rajan_Verma wrote:
> Give sheet name in Inverted Commas ( “ “ )
>
>
>
> Rajan.
gt; From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
> On Behalf Of Domain Admin
> Sent: Saturday, April 14, 2012 12:33 AM
> To: excel-macros@googlegroups.com
> Subject: Re: $$Excel-Macros$$ Ok I know I should be getting this by now but
> why does this ass
Ah you wax poetic. I get what you say, but I started out using the
With version and just took away the With and End With
and placed the sheets(rawdata) in front of .range.
So why did the With version work? Why does it not have the same
issue? When the With is outside the assignment then Cells
array variable and getting (column, row) would be very
> confusing.
>
> And then, any array you assigned to a range would essentially be transposed
> automatically, too, if you could reassign the array you retrieved from a
> range.
>
> Anyway, sorry!
> Asa
>
> -Original Message--
7;m a little surprised that including an array constant in
> the expression doesn't in itself work. Something I had tried.
>
> Asa
>
> -Original Message-
> From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
> On Behalf Of Domain Admin
&g
round(" &
tmprange.Address & ",2)))")
On Wed, Apr 11, 2012 at 11:56 PM, Rajan_Verma wrote:
> Use it with IF and Len()
>
> Rajan,
>
> -Original Message-
> From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
> On Behalf Of Domain Ad
If this was a compiled language I would say compiler error and could
probably get
around it by just sticking some dummy statement in front of it. Which
is essentially
what you did with the double transpose.
On Wed, Apr 11, 2012 at 5:53 PM, Asa Rossoff wrote:
> To retrieve a multi-column array I
d make you think of it that being the case?
> Sorry, makes me think of what being the case?
>
> -Original Message-
> From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
> On Behalf Of Domain Admin
> Sent: Wednesday, April 11, 2012 6:18 PM
> To: excel
>
> -Original Message-
> From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
> On Behalf Of Domain Admin
> Sent: Wednesday, April 11, 2012 7:24 PM
> To: excel-macros@googlegroups.com
> Subject: Re: $$Excel-Macros$$ Argh. Previously posted evaluat
anges, not arrays...
> transpose(transpose(
>
> Best answer I can come up with :)
> Asa
>
> -Original Message-
> From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
> On Behalf Of Domain Admin
> Sent: Wednesday, April 11, 2012 6:57 P
Well you completely lost me. I do not see the reason for and output
and input array.
I also see a redim on a range and I thought they only worked on arrays.
Any I expected to do something much simpler (syntax may not be correct here)
dim outputarray(somerowsize, 8)
dim resultsrange as range
for
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
> On Behalf Of Domain Admin
> Sent: Wednesday, April 11, 2012 6:18 PM
> To: excel-macros@googlegroups.com
> Subject: Re: $$Excel-Macros$$ Argh. Previously posted evaluate function
> replacing loop has unnoticed major error
he original array. No idea why
> this was needed..
>
> -Original Message-
> From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
> On Behalf Of Domain Admin
> Sent: Wednesday, April 11, 2012 6:02 PM
> To: excel-macros@googlegroups.com
> Subje
Ok that does appear to work, but I don't get it. How does nested
transposes function
to make this work correctly? It would appear to the novice that all
you did was transpose
it then transpose it back then whatever...
Is transposing an array the answer to my other new post about redim preserve?
Running your version rounded the first column correctly, but then
copied that column
into the other 4 columns. So it would seem unless there is a better
way to use
index I would have to set a separate range for each column and then evaluate
each range.
On Wed, Apr 11, 2012 at 5:38 PM, Domain
I thought is might be that ROUND could only work on one value.
Blaming yourself when I did not notice that my entire range was being
clobbered is magnanimous of you.
One thing you said is incorrect. The range is not just one column (is
that the problem).
It is 5 columns. I read the help on index
, 7))
For Each onecell In tmprange
onecell.Value = .Evaluate("round(" & onecell.Address & ",2)")
Next onecell
End With
On Wed, Apr 11, 2012 at 2:21 PM, Domain Admin wrote:
> Actually it does not work. I was not paying attention. I
, Rajan_Verma wrote:
> Yes you were right, But now you also believe that it is most efficient way
> to complete task and avoid loop. :)
>
> Thanks
> Rajan.
>
> -Original Message-
> From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
> On Behalf
Rajan the evaluate method did turn out to be the most efficient of the
3 methods I tried by a wide margin.
Here is the code used which took only 13.7% of the time of the next
best method and only 11.6% of the
loop method. Pretty much as you described it but the Len funtion is
not needed. I belie
for the benefit of all.
>
>
>
>
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguille...@gmail.com
> -Original Message- From: Domain Admin
> Sent: Monday, April 09, 2012 1:42 PM
> To: excel-macros@googlegroups.com
>
> Subject: Re: $$Excel-Mac
ter:
> YES. You got it.
> What scope of other procedures depends on whether you use Public or
> Private/Dim to declare the range obejct at the module level. Private/Dim
> and the value will be accessible from any procedure in the module, Public
> from at least any procedure in th
. It also saves us the trouble of creating a workbook
> ourselves and trying to set it up the same way and generate test data if we
> want to test your code.
> Asa
>
> -Original Message-
> From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
> On Behal
iginal Message-
> From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
> On Behalf Of Domain Admin
> Sent: Monday, April 09, 2012 12:16 PM
> To: excel-macros@googlegroups.com
> Subject: Re: $$Excel-Macros$$ Could this loop be replaced by some range
> meth
procedures, does the
calling procedure inherit the assignments?
On Mon, Apr 9, 2012 at 11:17 AM, Domain Admin wrote:
> 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 module
> level, then is BarDate the range
esAid Software
> dguille...@gmail.com
> -----Original Message- From: Domain Admin
> Sent: Monday, April 09, 2012 1:42 PM
> To: excel-macros@googlegroups.com
>
> Subject: Re: $$Excel-Macros$$ Could this loop be replaced by some range
> method?
>
> No need. I figured out
----Original Message- From: Domain Admin
> Sent: Monday, April 09, 2012 11:50 AM
>
> To: excel-macros@googlegroups.com
> Subject: Re: $$Excel-Macros$$ Could this loop be replaced by some range
> method?
>
> That link fails. What you said below I think gives a range that
&g
Never mind, figured it out.
On Mon, Apr 9, 2012 at 11:06 AM, Domain Admin wrote:
> Ok this works. But why does it work fine if I replace activesheet
> with sheets(rawdata)
> but I can not replace application with sheets(rawdata).worksheetfunction
>
> which gives error object d
artype ' uses module scope
>
> In a procedure they can be declared:
> Dim varname As vartype ' uses local (procedure) scope
> Static varname as vartype ' uses local (procedure) scope and retains value
> between calls
>
> Then use Set if you are assigning a
.SpecialCells(xlConstants, xlNumbers)
>
> c.Value = Application.Round(c, 2)
>
> ‘OR vba round which may round down instead of rounding OFF
> ‘ c = Round(c, 2)
>
> Next c
> End Sub
>
>
>
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguille..
nd Sub
>
>
>
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguille...@gmail.com
> -Original Message- From: Domain Admin
> Sent: Monday, April 09, 2012 11:21 AM
> To: excel-macros@googlegroups.com
> Subject: Re: $$Excel-Macros$$ Could this loo
t;
> Rajan.
>
> -Original Message-
> From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
> On Behalf Of Domain Admin
> Sent: Apr/Mon/2012 10:03
> To: excel-macros@googlegroups.com
> Subject: Re: $$Excel-Macros$$ Could this loop be replaced by some r
to:excel-macros@googlegroups.com]
> On Behalf Of Domain Admin
> Sent: Apr/Mon/2012 09:13
> To: excel-macros@googlegroups.com
> Subject: Re: $$Excel-Macros$$ Could this loop be replaced by some range
> method?
>
> Thanks for the help Rajan but your syntax confuses me. It appears a
All my values are numbers so can I eliminate the specialcells part?
To define the ranges can I use
set columnA = columns(worksheetfunction.match("columnA", .rows(1),0))
using the real name for each column and then use columnA as the
range?
But that would I think give me the entire column and I
Thanks for the help Rajan but your syntax confuses me. It appears as
if you are trying to create an excel function inside of VBA which
does not make sense to me.
On Mon, Apr 9, 2012 at 5:51 AM, Rajan_Verma wrote:
> If range have only numeric value then you can use this to avoid loops :
>
>
>
>
bal or optionally workbook scope
> Private varname As vartype ' uses module scope
> Dim varname As vartype ' uses module scope
>
> In a procedure they can be declared:
> Dim varname As vartype ' uses local (procedure) scope
> Static varname as vartype ' uses
This problem is solved.
On Mon, Apr 9, 2012 at 5:52 AM, Rajan_Verma wrote:
> Can you explain More to get desired result?
>
> Rajan.
>
> -Original Message-
> From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
> On Behalf Of Domain Admin
>
Never mind, answered my own question.
On Sun, Apr 8, 2012 at 3:48 PM, tangledweb wrote:
> I would like to have a userform on a data sheet for setting parameters and
> controls.
> But if that useform is considered part of the range by the usedrange
> function then I will
> have to put it on a sepa
that still functions.
When you use SET to create pointers they can only be done inside a
Sub. If you want to use them in another SUB do you have to do the SET
again or if in the same module will the SET be know by other Subs?
On Sat, Apr 7, 2012 at 10:55 PM, Domain Admin wrote:
> Not sure
of practice it's good to avoid loops when
> there are good alternatives, but if the speed is not an issue in your case
> whatever works.
>
>
> -Original Message-
> From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
> On Behalf Of Domain Admin
xplicit make it so it is difficult to predict Excel's behavior.
>
> Also, the change in Excel 2007 to massive sheet sizes creates new memory and
> compatibility issues.. not everything can handle 10 data points (or
> more when working with multiple columns).
>
> Asa
>
iling.
>
> Asa
> -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
; ReDim Preserve to enlarge it as you go.
>
> -Original Message-
> From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
> On Behalf Of Domain Admin
> Sent: Saturday, April 07, 2012 9:10 PM
> To: excel-macros@googlegroups.com
> Subject: Re: $$Excel-Mac
is based on, and they are in many respects
> the same language.
>
> Did you see the recent posts in the group here with links to Excel/VBA
> reference sites?
>
> Asa
>
> -Original Message-
> From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
eeded in advance since it is time consuming to create range objects
> repeatedly):
> startrawdata =
> WorksheetFunction.Match(Sheets(ContangoSource).Cells(2,
> ConDate).Value, Sheets(RawData).Columns(BarDate), 0)
> (5) In your rawdata loop, there is no need for parenthesis aroun
rything, but help is
> available.
>
> Asa
>
> -Original Message-
> 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: $$Exc
happened.
> One way besides a syntactical bug in your code is that the name of a sheet
> isn't exactly what you think it is, or the value of a cell isn't exactly
> what you think it is (or even what it looks to be). There is the occasional
> VBA bug, too, that sometimes requ
preadsheet
=MATCH(A2,ContangoSource!A:A)
returns the correct value
On Sat, Apr 7, 2012 at 5:50 PM, Domain Admin wrote:
> Well the usedrange problem is solved. I printed out just the cell
> count of the used range and it indicated there was a column with data
> somehow even though invisi
he correct answer just plugging it into the
spreadsheet.
On Sat, Apr 7, 2012 at 5:17 PM, Domain Admin wrote:
> 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 in the sheet ex
tire rows: 1:1048576
>
> The latter naturally, with the definition of .Cells at hand, is the entire
> worksheet. It could also be described in terms of columns: A:XFD
> Or in terms of cells: A1:XFD1048576
>
> Asa
>
> -Original Message-
> From: excel-macros@googlegr
Oh and don't worry about that loop. Once the first match is found if
that ever works there will never be any gaps. There will always be
one or more copies of dates in rawdata that match the dates in
contangosource. No gaps.
On Sat, Apr 7, 2012 at 5:00 PM, Domain Admin wrote:
&g
PM, Domain Admin wrote:
> Writing that book should be easy. Just collect all your question and
> answer thread and put them in a book.
>
> I do have Option Explicit
>
> The current usedrange does seem to return the correct range looking at
> the address so
> that clearcont
ion.Match instead of looping through the whole
> range. If that would result in fewer iterations it could be significantly
> faster.
>
> Asa
>
>
> -Original Message-
> From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
> On Behalf Of Domain Adm
Thanks but this thread is solved/ended
On Sat, Apr 7, 2012 at 4:27 PM, dguillett1 wrote:
> Provide a file with examples
>>
>>
>
>
>
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguille...@gmail.com
> -Original Message- From: Domain A
.
>
> Also, please post procedure or file, otherwise we have to make a lot of
> guesses and assumptions
>
> Asa
>
> -Original Message-
> From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
> On Behalf Of Domain Admin
> Sent: Saturday, April
find the
> end of a column. Seems like should be simple.
>
> Provide a file with examples
>
>
>
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguille...@gmail.com
> -Original Message-
> From: Domain Admin
> Sent: Saturday, April 07, 2012 3:42
use:
> BarDate
>
> For more help pleas post whole procedure
>
> -Original Message-
> From: excel-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
separate by blanks, you're back to depending on .currentregion or the using
> method #1 on 1 or a few rows (i.e. row 1 with the headers).
>
> Asa
>
> -Original Message-
> From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
> On Behalf O
Seems like should be simple.
>
> Provide a file with examples
>
>
>
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguille...@gmail.com
> -Original Message-
> From: Domain Admin
> Sent: Saturday, April 07, 2012 3:42 PM
> To: excel-macros@googlegrou
t).delete
> end with
>
> Asa
>
> -Original Message-
> From: excel-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$$
, dguillett1 wrote:
> Provide a file with examples
>
>
>
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguille...@gmail.com
> -Original Message- From: Domain Admin
> Sent: Saturday, April 07, 2012 3:42 PM
>
> To: excel-macros@googlegroups.com
>
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 wrote:
> Yes and no, but if formats count as part of usedrange then this method
> wi
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 wrote:
> Did you delete rows using the row indicat
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 (excep
this case match function will return a result if it value match in range,
> but it will not return an error if values does not match in Range..
> On Error is just to escape from error..
>
> Rajan.
>
> -Original Message-
> From: excel-macros@googlegroups.com [mailto:excel
In this case it will be impossible for the match function to not find
a match but thanks. No idea on why the assignment
to stoprawdata fails?
On Sat, Apr 7, 2012 at 1:17 PM, Rajan_Verma wrote:
> If match function dost not find any value it return an error , to escape
> this error use the followi
thanks
On Fri, Apr 6, 2012 at 5:38 AM, Rajan_Verma wrote:
> Make a breakpoint at the line.Select the Object and Press Shift + F9 ,
> object will be added in Watch Window.. you can find all assigned properties
> of that object
>
> For methods , when you use DOT (.) follow by object VBE provides l
I already did and it worked as expected, thanks again.
On Fri, Apr 6, 2012 at 5:03 AM, dguillett1 wrote:
> Try it. YES!!
>
>
>
>
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguille...@gmail.com
> -----Original Message- From: Domain Admin
> Se
second object to a variable declared properly
> resolves the issue.
>
> Asa
>
>
> -Original Message-
> From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
> On Behalf Of Domain Admin
> Sent: Thursday, April 05, 2012 10:01 PM
> To: excel-macro
fy you have no syntax errors bugging VBA.
>
>
> -Original Message-
> From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
> On Behalf Of Domain Admin
> Sent: Thursday, April 05, 2012 9:31 PM
> To: excel-macros@googlegroups.com
> Subject: Re: $$Exce
= shorttextintellisense comes awake when I enter
the period after activecell but when I type
Sheets(Results).UsedRange.Offset(1).ClearContents I do not get
intellisense help after the period following usedrange.
On Thu, Apr 5, 2012 at 9:48 PM, Domain Admin wrote:
> My help already set to offl
1 - 100 of 116 matches
Mail list logo
