more effective script

Hi All,

Since I can't get my screen to lock in openoffice (while using VBA), I have to find another way to speed things up.
As you can see below, my scripting is very very poor and I was wondering how I can speed up the script.

I was thinking about tricks to let it run faster, but I don't really come up with solutions.

Please Login or Register  to view this content.

The first loop could be done in one line.

Please Login or Register  to view this content.

Originally Posted by Norie

The first loop could be done in one line.

Please Login or Register  to view this content.

Nice
I added iRow, jRow, kRow, lRow so I don't have to find the last row the whole time. That made things faster too (but now I notice it only adds 1/3th of the entries )
Noticed that by trying things I just removed 2/3th of my data... put it back and noticed no speed difference >.<

What I posted is a direct replacement for the loop it shouldn't cause any change in what the rest of the code does.

So you are using the the code I posted but it makes no difference?

What, in words, is the code meant to do?

Delete previous values in Sheet B
Copy all unique values from Sheet A (columns C,D,E,H) to Sheet B (columns A,B,C,D) that have a length of more then 1

+remove some older input fields
+count the last row number of Sheet B

The reason why I need it:
Column 1: Validation for cell x
Column 2: Validation for cell y
Column 3: Validation for cell z
Column 4: Validation for cell a

Based on the selection on x,y,z,a another macro will run

I was thinking that maybe with a formula in the validation the same thing can be achieved
- if length is more then 1
- Sort ascending (will automatically remove duplicates)

Someone knows if and how this can be done?

If you want to copy unique values with length > 1 then you could use advanced filter.

OpenOffice does support advanced filtering and as far as I know it works much the same as advanced filter in Excel, manually anyway.

I'm thinking about persuading them to buy Excel2010 and if they don't want to spend money on it, I might buy it for them myself.

Change my ods file to xlsm. Had to redo all formatting and had to take a look at the macro's. (still working on getting it back to the way it was working before the change).

AdvancedFilter now ofcourse is working, BUT it seems to fail on 1 column

Please Login or Register  to view this content.

Column E in ws to Column C in VA remains blank, but there are of course more then enough unique files.

Now I still don't know how to get the filter: If length > 1 then copy
And I don't know why that one column is being a ***** (If I add a testvalue in E3, then it does work.. But I don't want that -.-)

Any help is welcome; (I use Excel2010, VBA script now)


-- update --
Changed the script a little so that Column E does work. For the length > 1; still no solution found :/

Please Login or Register  to view this content.

Tried something like above