Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recorded macro selection widened
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. -- Walter Briscoe |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recorded macro selection widened
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. -- Walter Briscoe -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recorded macro selection widened
In message of Thu, 14 May 2009
12:11:32 in microsoft.public.excel.programming, Dave Peterson writes 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. -- Walter Briscoe -- Walter Briscoe |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recorded macro selection widened
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. Walter Briscoe wrote: In message of Thu, 14 May 2009 12:11:32 in microsoft.public.excel.programming, Dave Peterson writes 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. -- Walter Briscoe -- Walter Briscoe -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recorded macro selection widened
In message of Fri, 15 May 2009
09:26:04 in microsoft.public.excel.programming, Dave Peterson writes 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 wris****ch 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. Walter Briscoe wrote: In message of Thu, 14 May 2009 12:11:32 in microsoft.public.excel.programming, Dave Peterson writes 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. -- Walter Briscoe -- Walter Briscoe -- Walter Briscoe |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro recorded... tabs & file names changed, macro hangs | Excel Worksheet Functions | |||
Pause recorded macro for user selection of menu option | Excel Programming | |||
Recorded macro | Excel Discussion (Misc queries) | |||
Recorded single column insert in macro. Macro inserts two not one | Excel Programming | |||
Creating a macro which presses a button containing a recorded macro | Excel Programming |