Search:
Type: Posts; User: Earlien
Search:
Search took 0.03 seconds.
-
Did some more research.
Apparently, Cell Comments were called Cell Notes in Excel 2000 and earlier. So .ClearNotes does nothing more than .ClearComments.
-
Ok, thats kinda helpful. But I know that .ClearNotes deletes more than just these 'sound notes' (whatever they were) because I've tested it and I know it deletes comments too. Does it delete...
-
The code I have used in the past to do this is:
Private Declare Function GetTickCount Lib "kernel32.dll"() As Long
Private Sub Time_Code()
Dim t as 'Long
t = GetTickCount
...
-
Hi all,
I have been looking into various methods for deleting cells or contents of cells and am a little confused by the ClearNotes method. Originally I thought it may have been legacy code and...
-
Thanks dmaruca.
I find it odd that a union of ranges is not a valid range. After all, they are valid for things like data sources for charts. I guess I will have to create an edited table...
-
I've just tested using a filtered range as the data source, but it picks up up the hidden/filtered rows as well. Well, doing it manually anyway - not sure if you can specify only visible rows in the...
-
Ok, I've discovered that if I only use the data section range, the header is automatically included in the range, so there is no need to use the union of the header and data range. E.g.
...
-
Hi. I am having trouble specifying a range as the data source for a pivot cache. It works fine if I specify a simple range, but fails when I use a union of ranges as a range. I am getting a...
-
I still have not been able to solve this.
In addition to the above attempts, I have also tried capturing the error by evaluating the problematic code with functions like IsError and IsNull but VB...
-
Rather than start a new thread, I am extending this thread as I have the same problem and the solutions suggested above do not work.
Open a new workbook, throw some values into column 1 of...
-
I'm glad someone else found this useful too :)
-
Yes, that works. I knew there had to be a simple solution. :)
-
Basically, title says it all. I would have thought this to be a fairly easy thing to find, but I cannot find the answer to this in any documentation, or on any forums.
This is my first time...
-
Thanks LaVolpe and SolnArchRet for the input.
I don't pretend to understand what actually takes place in the background workings of the program, such as whether the value is actually passed or...
-
Thats simply because myarr is not declared so VBA handles non-declared variables by making them variant. Same as:
Sub Test2()
i = 10
End Sub
In the Locals window, 'i' appears as...
-
Going back to one of your comments in post #3...
May I ask where you got this from? I went back to the Microsoft website which describes the Split function as
Function Split(
ByVal...
-
I realise there are a few ways to solve the problem, Westconn, as you suggested. However, it is the principle that concerns me. These solutions may work just fine for this problem, but I might find...
-
Hi Steve. Sorry, I may have mislead you a little bit as I copied and pasted the code and forgot to remove the variable SC_Array1(). Basically, I'm trying to achieve the same solution without using...
-
Well, I also tried using a conversion function to coerce it into a string as follows:
If Search_Array(Test, CStr(Split("Brisbane,Bundaberg,Cairns,Ipswich,Mackay,Rockhampton,Townsville", ",")))...
-
As a follow up thought...
The only thing I can think of is that the split function isn't returning an array unless it is explicitly assigned to one.
But it seems like the two scenarios are:
...
-
I'm using a split function to create an array of strings (based on Darren M's suggestion in this thread). I have an array which I 'pre-fill' at the start of the code and later use a search function...
-
Cool, thanks for the feedback Westconn.
The main reason I don't use the .end() function is because I often work with data in which rows or columns may only have one or a few values, thereby giving...
-
Coincidentally, I found another one of your posts Westconn in a similar thread, but your suggestion for finding the First used row seems highly inadequate:
firstrow = range("a9:f99").row
...
-
Alright, I changed the code for calculating the first row to:
First_Row = XL_Ws.Cells.Find(what:="*", After:=XL_Ws.Cells(Rows.Count, Columns.Count), SearchDirection:=xlNext,...
-
I've seen many people ask how to find the "actual" used range of a worksheet on other forums, because the method that most people would initially think of will includes cells that have had values...
-
-
Hi Opus. Perhaps the following can allay your confusion :)
Suppose you want to store the integers 1 to 10 in an array. This can be done easily with a loop right?
Private Sub Test1()
...
-
Well, I tried implementing my idea of declaring a second array as an integer, then assigning the values of the first array to the second, then setting the second array to nothing, effectively freeing...
-
Good advice :) I may try this when I'm done and post the results.
-
Its true, I was originally looking at any sort of array, strings, integers or otherwise. However, I think I may only end up requiring integers in the array, so thought I'd push this split function...
-
Hmm, I tried declaring the array as an integer then removing the "A,B,C" part but it returns type mismatch error. I'm guessing that's because the first expression in the split function must be...
-
Aha!! That's exactly what I was looking for :) I must look into this split function sometime and find out exactly what its doing (although it seems fairly self-explanatory).
Thanks Darren, you're...
-
Ok, I've discovered it works if you declare the array as variant AND as a dynamic array. I doesn't appear to work for fixed-length arrays, no matter what data type you use.
Is there no better...
-
Actually, I tried this and I still get the compile error "Can't assign to array."
Dim Rows_Array(1 To 7) As Variant
Rows_Array = Array(5, 9, 14, 19, 25, 29, 34)
Any other ideas?
-
Well, naturally any good programmer wishes to minimise memory used and the time taken to run the code (i.e. less lines of code) :). Based on your first answer and the lack of other replies, I'm...
-
Thanks for the reply Opus.
I can't say I'm satisfied with that answer though because I know it would work declaring the array as a string and entering the values separately. There has to be a more...
-
I haven't worked with data arrays in VBA for a while, but thought that it would be a fairly simple procedure to assign values to a one-dimensional array in a single line of code (as opposed to...
-
Umm...how do I edit the thread title to mark it as resolved?
Edit: Nvm, worked it out . You have to use the "Go Advanced" options after editing the post. :)
-
Sorry for the delayed response - I had other projects to complete before Christmas and have only returned to the office yesterday.
To anyone reading this thread - implementing si_the_geek's...
-
Thank you si_the_geek,
I had no idea you had to specify this inside the range as well!
It seems kind of superfluous that you would need to specify what sheet the cells are on if the sheet for...
|
Click Here to Expand Forum to Full Width
|