PDA

View Full Version : Select several lines



Ago
09-08-2008, 04:51 AM
really stupid question, but i cant find the answer when im searching.

how do i select several lines in a macro?
lets say i want to select line 1, 4 and 9.
in excel i would hold CTRL and click on the lines.

how is it done in VBA?

i need to "append" the selection during the run of the macro.
so in the start of the macro line 1 might be the only selected line, and by the end of it there might be hundreds of lines selected.

Bob Phillips
09-08-2008, 05:16 AM
Range("A1,A4,A9").EntireRow.Select

'or

Union(Rows(1), Rows(4), Rows(9)).Select

Ago
09-08-2008, 08:13 AM
but this means i need to know what lines should be selected.

im trying to speed up my macro that removes duplicated lines.

each time it finds a duplicate it removes the line.
this can be done faster if it removes "all" lines at once.

this can be done in two ways as i see it.

"mark" each line and count how many marked rows you get, and then sort the list so the marked lines comes at the top and delete them.
thats the way i do it today, but this means i have to sort the list twice. with 27000 lines that takes time!

or:

select each line as the macro runs and at the end of the macro do a selection.entirerow.delete
this means i only have to sort the list once.
i think this will speed it up a bit


my first version selected each line one at the time and deleted them.
that was horrible!

Kenneth Hobs
09-08-2008, 08:31 AM
That is another animal. Does advanced filter not work?

Bob Phillips
09-08-2008, 08:48 AM
but this means i need to know what lines should be selected.

... but EXACTLY what you asked for.

Ago
09-08-2008, 09:04 AM
... but EXACTLY what you asked for.

True, if you only read HALF of my first post.



i need to "append" the selection during the run of the macro.
so in the start of the macro line 1 might be the only selected line, and by the end of it there might be hundreds of lines selected.

Ago
09-08-2008, 09:09 AM
That is another animal. Does advanced filter not work?

both yes and no.
it will work but since the macro is doing other calculations at the same time it will be slower to use advanced filter.
if i should use advanced filter i would have to do the other calculations first then advanced filter and remove lines.

Bob Phillips
09-08-2008, 09:35 AM
True, if you only read HALF of my first post.

If you ask a generic question, you get a generic answer. There was ABSOLUTELY no way we could have guessed from your first post the information you divulged in a later post.