Recorded macro selection widened

[Walter Briscoe]

I am running Excel 2003.

I have data which looks like this

A B C D E F G
1 Title on all columns
2 A2 B2 C2 D2 E2 F2 G2
3 A3 B3 C3 D3 E3 F3 G3
4 A4 B4 C4 D4 E4 F4 G3
5 A5 B5 C5 D5 E5 F5 G4
6 A6 B6 C6 D6 E6 F6 G6
....

I record a macro which hides row 1 and selects column C.
When I step through the code, the whole sheet seems to be selected.
I have a workaround; I would like an explanation.

Rows("1:1").EntireRow.Hidden = True ' Hide first row
Columns("C:C").Select ' "selects" Columns("A:G")
Range("C2", Range("C2").End(xlDown)).Select ' "selects" what I want.

 

[Dave Peterson]

Merged cells react differently in different versions of excel.

To make matters worse, merged cells react differently in code than in the user
interface (as you've seen).

I do my best not to use merged cells.

 

[Walter Briscoe]

In message <[email protected]> of Thu, 14 May 2009
12:11:32 in microsoft.public.excel.programming, Dave Peterson

Dave,
Thanks for a quick, explanatory reply.

The .xls is produced by someone else. What are merged cells?
I assume they are not just cells overflowing into empty neighbours.

In the same sheet. I have a vlookup speed problem. My data is sorted in
another file and I setup a 2 column, 500 row array with code like this:
Range("E2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2]," & _
"'folder\[file.xls]" & "Sheet1 '!named_range,27)"
Selection.Copy
ActiveSheet.Paste Destination:=Range("E2:F" & LastRow)

That Paste takes about 90 seconds; doing it by manually selecting a
range and pasting makes no difference; moving the merged cell row to
another sheet makes no difference. My newly-acquired understanding is
that the vlookup should not be slow with Range_lookup True or
(equivalently) omitted and that it might be slow with it as False -
False is similarly slow.
My 300 row data is sorted.

Is 10 lookups a second a reasonable speed?
If not, am I committing an obvious naivety?

 

[Dave Peterson]

Merged cells is a format that allows you to make multiple cells look like a
single cell.

Start a new worksheet
Select A1:E1
Format|Cells|alignment tab|check merge

Notice how those 5 cells now look like one.

This is useful technique to make things look pretty.

But it's a miserable thing to do when you want to be useful. It can screw up
copy|paste, filter, macros... I do my best not to go near merged cells.

======
300 rows makes Range("E2:F" & LastRow) about 600 cells. That means that excel
has to go off to your closed workbook to retrieve the values.

Try opening your file in the same instance of excel and watch how fast excel
reevaluates those formulas.

In message <[email protected]> of Thu, 14 May 2009
12:11:32 in microsoft.public.excel.programming, Dave Peterson

Dave,
Thanks for a quick, explanatory reply.

The .xls is produced by someone else. What are merged cells?
I assume they are not just cells overflowing into empty neighbours.

In the same sheet. I have a vlookup speed problem. My data is sorted in
another file and I setup a 2 column, 500 row array with code like this:
Range("E2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2]," & _
"'folder\[file.xls]" & "Sheet1 '!named_range,27)"
Selection.Copy
ActiveSheet.Paste Destination:=Range("E2:F" & LastRow)

That Paste takes about 90 seconds; doing it by manually selecting a
range and pasting makes no difference; moving the merged cell row to
another sheet makes no difference. My newly-acquired understanding is
that the vlookup should not be slow with Range_lookup True or
(equivalently) omitted and that it might be slow with it as False -
False is similarly slow.
My 300 row data is sorted.

Is 10 lookups a second a reasonable speed?
If not, am I committing an obvious naivety?

Merged cells react differently in different versions of excel.

To make matters worse, merged cells react differently in code than in the user
interface (as you've seen).

I do my best not to use merged cells.

 

[Walter Briscoe]

In message <[email protected]> of Fri, 15 May 2009
09:26:04 in microsoft.public.excel.programming, Dave Peterson

I am now informed about merging cells and the disadvantages of
implicitly opening a lookup file.
My 90 second task took about 2 seconds after I explicitly opened the
data file. (I'm currently using a wristwatch to time operations
With the file open, the implicit code got a 1004

I had not realized that vlookup from a closed workbook effectively opens
the workbook as a hidden file, does the lookup and closes the workbook.
I had not found anything about this cause of slowness.

I would like to hide the data file. WorkBooks.Open does not seem to have
a relevant parameter and I can't find anything obvious in the properties
or methods of the workbook object.

[I must investigate the boilerplate of my replies. Your reply shows as
being before my posting. 09:26:04 (-0500) is rendered as 09:26:04.]

Thanks again for your help.

Merged cells is a format that allows you to make multiple cells look like a
single cell.

Start a new worksheet
Select A1:E1
Format|Cells|alignment tab|check merge

Notice how those 5 cells now look like one.

This is useful technique to make things look pretty.

But it's a miserable thing to do when you want to be useful. It can screw up
copy|paste, filter, macros... I do my best not to go near merged cells.

======
300 rows makes Range("E2:F" & LastRow) about 600 cells. That means that excel
has to go off to your closed workbook to retrieve the values.

Try opening your file in the same instance of excel and watch how fast excel
reevaluates those formulas.

In message <[email protected]> of Thu, 14 May 2009
12:11:32 in microsoft.public.excel.programming, Dave Peterson

Dave,
Thanks for a quick, explanatory reply.

The .xls is produced by someone else. What are merged cells?
I assume they are not just cells overflowing into empty neighbours.

In the same sheet. I have a vlookup speed problem. My data is sorted in
another file and I setup a 2 column, 500 row array with code like this:
Range("E2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2]," & _
"'folder\[file.xls]" & "Sheet1 '!named_range,27)"
Selection.Copy
ActiveSheet.Paste Destination:=Range("E2:F" & LastRow)

That Paste takes about 90 seconds; doing it by manually selecting a
range and pasting makes no difference; moving the merged cell row to
another sheet makes no difference. My newly-acquired understanding is
that the vlookup should not be slow with Range_lookup True or
(equivalently) omitted and that it might be slow with it as False -
False is similarly slow.
My 300 row data is sorted.

Is 10 lookups a second a reasonable speed?
If not, am I committing an obvious naivety?

Merged cells react differently in different versions of excel.

To make matters worse, merged cells react differently in code than
in the user
interface (as you've seen).

I do my best not to use merged cells.



Walter Briscoe wrote:

I am running Excel 2003.

I have data which looks like this

A B C D E F G
1 Title on all columns
2 A2 B2 C2 D2 E2 F2 G2
3 A3 B3 C3 D3 E3 F3 G3
4 A4 B4 C4 D4 E4 F4 G3
5 A5 B5 C5 D5 E5 F5 G4
6 A6 B6 C6 D6 E6 F6 G6
...

I record a macro which hides row 1 and selects column C.
When I step through the code, the whole sheet seems to be selected.
I have a workaround; I would like an explanation.

Rows("1:1").EntireRow.Hidden = True ' Hide first row
Columns("C:C").Select ' "selects" Columns("A:G")
Range("C2", Range("C2").End(xlDown)).Select ' "selects" what I want.