Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making a process faster
I have about 40k rows of info in a sheet. This goes to VP level
people. Now, they want the location specific information (25 locations) to go to the managers of those locations. So, I have to make 25 copies of the book, and delete all the rows that don't go to that location in each book. So, I cycle through the rows, checking the manager and clearing the row if it doesn't belong. Then I sort the remaining records to the top. This works fine, I'm just wondering if there is a more efficient (speedier) way to do this. FWIW, I'm doing this from MS Access 2003 strPathDest = strBIExt & "\InforceList" & strFileDate & "_" & ! SalesMgrFileName & ".xls" FileCopy strPathSrc, strPathDest Set xlBook = xlApp.Workbooks.Open(strPathDest) 'Grab Inforce and clean it out Set xlSheet = xlBook.Worksheets("Inforce") xlSheet.Activate intRow = 2 Do While xlSheet.Range(R1C1toA1(intRow, 18)).Value "" If xlSheet.Range(R1C1toA1(intRow, 18)).Value < ! SalesMgrXLName Then Rows(intRow).Select Selection.ClearContents End If intRow = intRow + 1 Loop Rows("2:65536").Select Selection.Sort Key1:=Range("C1"), Order1:=xlAscending, Header:=xlYes Range("A2").Select |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making a process faster
Why not create queries in Access then you can use those as a base for your
report? "mcescher" wrote in message ... I have about 40k rows of info in a sheet. This goes to VP level people. Now, they want the location specific information (25 locations) to go to the managers of those locations. So, I have to make 25 copies of the book, and delete all the rows that don't go to that location in each book. So, I cycle through the rows, checking the manager and clearing the row if it doesn't belong. Then I sort the remaining records to the top. This works fine, I'm just wondering if there is a more efficient (speedier) way to do this. FWIW, I'm doing this from MS Access 2003 strPathDest = strBIExt & "\InforceList" & strFileDate & "_" & ! SalesMgrFileName & ".xls" FileCopy strPathSrc, strPathDest Set xlBook = xlApp.Workbooks.Open(strPathDest) 'Grab Inforce and clean it out Set xlSheet = xlBook.Worksheets("Inforce") xlSheet.Activate intRow = 2 Do While xlSheet.Range(R1C1toA1(intRow, 18)).Value "" If xlSheet.Range(R1C1toA1(intRow, 18)).Value < ! SalesMgrXLName Then Rows(intRow).Select Selection.ClearContents End If intRow = intRow + 1 Loop Rows("2:65536").Select Selection.Sort Key1:=Range("C1"), Order1:=xlAscending, Header:=xlYes Range("A2").Select |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making a process faster
On May 4, 10:40*am, "Jellifish" wrote:
Why not create queries in Access then you can use those as a base for your report? "mcescher" wrote in message ... I have about 40k rows of info in a sheet. *This goes to VP level people. *Now, they want the location specific information (25 locations) to go to the managers of those locations. *So, I have to make 25 copies of the book, and delete all the rows that don't go to that location in each book. So, I cycle through the rows, checking the manager and clearing the row if it doesn't belong. *Then I sort the remaining records to the top. *This works fine, I'm just wondering if there is a more efficient (speedier) way to do this. *FWIW, I'm doing this from MS Access 2003 * * *strPathDest = strBIExt & "\InforceList" & strFileDate & "_" & ! SalesMgrFileName & ".xls" * * *FileCopy strPathSrc, strPathDest * * *Set xlBook = xlApp.Workbooks.Open(strPathDest) 'Grab Inforce and clean it out * * *Set xlSheet = xlBook.Worksheets("Inforce") * * *xlSheet.Activate * * *intRow = 2 * * *Do While xlSheet.Range(R1C1toA1(intRow, 18)).Value "" * * * *If xlSheet.Range(R1C1toA1(intRow, 18)).Value < ! SalesMgrXLName Then * * * * *Rows(intRow).Select * * * * *Selection.ClearContents * * * *End If * * * *intRow = intRow + 1 * * *Loop * * *Rows("2:65536").Select * * *Selection.Sort Key1:=Range("C1"), Order1:=xlAscending, Header:=xlYes * * *Range("A2").Select- Hide quoted text - - Show quoted text - Well, this is a small part of a bigger project, and we have five databases building sheets in this workbook, and then the last one makes the copies and cleans up each of the sheets. It's not a super big deal, because it is automated, and runs fine, I just wondered if this was the most efficient way to attack the problem. My programming background is in Access, and I'm becoming more confortable with Excel, but still have a ton to learn. Thanks, Chris M. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making a process faster
Two questions... One, does it matter if one manager can see another managers
data? Two, do you have a column set up show the manager for each row of data? If the answer is yes to both of those questions, then select the entire column with the manager's names in it and then click Data/Filter/AutoFilter on the menu bar. This will create a drop down in your header for that column... click the down arrow and select a manager's name from the list... all the other rows not belonging to that manager will be hidden. Can you make use of this idea? -- Rick (MVP - Excel) "mcescher" wrote in message ... On May 4, 10:40 am, "Jellifish" wrote: Why not create queries in Access then you can use those as a base for your report? "mcescher" wrote in message ... I have about 40k rows of info in a sheet. This goes to VP level people. Now, they want the location specific information (25 locations) to go to the managers of those locations. So, I have to make 25 copies of the book, and delete all the rows that don't go to that location in each book. So, I cycle through the rows, checking the manager and clearing the row if it doesn't belong. Then I sort the remaining records to the top. This works fine, I'm just wondering if there is a more efficient (speedier) way to do this. FWIW, I'm doing this from MS Access 2003 strPathDest = strBIExt & "\InforceList" & strFileDate & "_" & ! SalesMgrFileName & ".xls" FileCopy strPathSrc, strPathDest Set xlBook = xlApp.Workbooks.Open(strPathDest) 'Grab Inforce and clean it out Set xlSheet = xlBook.Worksheets("Inforce") xlSheet.Activate intRow = 2 Do While xlSheet.Range(R1C1toA1(intRow, 18)).Value "" If xlSheet.Range(R1C1toA1(intRow, 18)).Value < ! SalesMgrXLName Then Rows(intRow).Select Selection.ClearContents End If intRow = intRow + 1 Loop Rows("2:65536").Select Selection.Sort Key1:=Range("C1"), Order1:=xlAscending, Header:=xlYes Range("A2").Select- Hide quoted text - - Show quoted text - Well, this is a small part of a bigger project, and we have five databases building sheets in this workbook, and then the last one makes the copies and cleans up each of the sheets. It's not a super big deal, because it is automated, and runs fine, I just wondered if this was the most efficient way to attack the problem. My programming background is in Access, and I'm becoming more confortable with Excel, but still have a ton to learn. Thanks, Chris M. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making a process faster
On May 4, 12:22*pm, "Rick Rothstein"
wrote: Two questions... One, does it matter if one manager can see another managers data? Two, do you have a column set up show the manager for each row of data? If the answer is yes to both of those questions, then select the entire column with the manager's names in it and then click Data/Filter/AutoFilter on the menu bar. This will create a drop down in your header for that column... click the down arrow and select a manager's name from the list... all the other rows not belonging to that manager will be hidden. Can you make use of this idea? -- Rick (MVP - Excel) "mcescher" wrote in message ... On May 4, 10:40 am, "Jellifish" wrote: Why not create queries in Access then you can use those as a base for your report? "mcescher" wrote in message ... I have about 40k rows of info in a sheet. This goes to VP level people. Now, they want the location specific information (25 locations) to go to the managers of those locations. So, I have to make 25 copies of the book, and delete all the rows that don't go to that location in each book. So, I cycle through the rows, checking the manager and clearing the row if it doesn't belong. Then I sort the remaining records to the top. This works fine, I'm just wondering if there is a more efficient (speedier) way to do this. FWIW, I'm doing this from MS Access 2003 strPathDest = strBIExt & "\InforceList" & strFileDate & "_" & ! SalesMgrFileName & ".xls" FileCopy strPathSrc, strPathDest Set xlBook = xlApp.Workbooks.Open(strPathDest) 'Grab Inforce and clean it out Set xlSheet = xlBook.Worksheets("Inforce") xlSheet.Activate intRow = 2 Do While xlSheet.Range(R1C1toA1(intRow, 18)).Value "" If xlSheet.Range(R1C1toA1(intRow, 18)).Value < ! SalesMgrXLName Then Rows(intRow).Select Selection.ClearContents End If intRow = intRow + 1 Loop Rows("2:65536").Select Selection.Sort Key1:=Range("C1"), Order1:=xlAscending, Header:=xlYes Range("A2").Select- Hide quoted text - - Show quoted text - Well, this is a small part of a bigger project, and we have five databases building sheets in this workbook, and then the last one makes the copies and cleans up each of the sheets. It's not a super big deal, because it is automated, and runs fine, I just wondered if this was the most efficient way to attack the problem. *My programming background is in Access, and I'm becoming more confortable with Excel, but still have a ton to learn. Thanks, Chris M.- Hide quoted text - - Show quoted text - Nope, can't use autofilters because they can't see each other's information. That's why I'm deleting the records. Thanks |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making a process faster
On May 4, 12:22*pm, "Rick Rothstein"
wrote: Two questions... One, does it matter if one manager can see another managers data? Two, do you have a column set up show the manager for each row of data? If the answer is yes to both of those questions, then select the entire column with the manager's names in it and then click Data/Filter/AutoFilter on the menu bar. This will create a drop down in your header for that column... click the down arrow and select a manager's name from the list... all the other rows not belonging to that manager will be hidden. Can you make use of this idea? -- Rick (MVP - Excel) "mcescher" wrote in message ... On May 4, 10:40 am, "Jellifish" wrote: Why not create queries in Access then you can use those as a base for your report? "mcescher" wrote in message ... I have about 40k rows of info in a sheet. This goes to VP level people. Now, they want the location specific information (25 locations) to go to the managers of those locations. So, I have to make 25 copies of the book, and delete all the rows that don't go to that location in each book. So, I cycle through the rows, checking the manager and clearing the row if it doesn't belong. Then I sort the remaining records to the top. This works fine, I'm just wondering if there is a more efficient (speedier) way to do this. FWIW, I'm doing this from MS Access 2003 strPathDest = strBIExt & "\InforceList" & strFileDate & "_" & ! SalesMgrFileName & ".xls" FileCopy strPathSrc, strPathDest Set xlBook = xlApp.Workbooks.Open(strPathDest) 'Grab Inforce and clean it out Set xlSheet = xlBook.Worksheets("Inforce") xlSheet.Activate intRow = 2 Do While xlSheet.Range(R1C1toA1(intRow, 18)).Value "" If xlSheet.Range(R1C1toA1(intRow, 18)).Value < ! SalesMgrXLName Then Rows(intRow).Select Selection.ClearContents End If intRow = intRow + 1 Loop Rows("2:65536").Select Selection.Sort Key1:=Range("C1"), Order1:=xlAscending, Header:=xlYes Range("A2").Select- Hide quoted text - - Show quoted text - Well, this is a small part of a bigger project, and we have five databases building sheets in this workbook, and then the last one makes the copies and cleans up each of the sheets. It's not a super big deal, because it is automated, and runs fine, I just wondered if this was the most efficient way to attack the problem. *My programming background is in Access, and I'm becoming more confortable with Excel, but still have a ton to learn. Thanks, Chris M.- Hide quoted text - - Show quoted text - I do have a column set up with the manager's name, that's how I'm determining whether to delete or keep the row. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making a process faster
How about trying something like the code below? What I do is first select
the entire block of data. Then filter for every manager EXCEPT the one you want to retain. Then select only the visible cells and delete their rows. Then unfilter, and you end up with only that manager's data. Very quick, especially if you turn off screen updating. I left out the part about making a copy of the worksheet first, to preserve the original data, but you already know that! HTH, Eric Sub Macro3() ' Unfilter the data ActiveSheet.ShowAllData ' Select all the relavent data, not including the header Range("A2:B9").Select ' your range will vary! ' Autofilter the data by EXCLUDING the desired manager's name Selection.AutoFilter Field:=1, Criteria1:="<John Doe", Operator:=xlAnd ' Select only the visible cells... Selection.SpecialCells(xlCellTypeVisible).Select ' Delete the selected rows Selection.EntireRow.Delete ' Select the header cell Range("A1").Select ' Unfilter ActiveSheet.ShowAllData End Sub |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making a process faster
On May 4, 2:05*pm, Eric G wrote:
How about trying something like the code below? *What I do is first select the entire block of data. *Then filter for every manager EXCEPT the one you want to retain. *Then select only the visible cells and delete their rows. * Then unfilter, and you end up with only that manager's data. *Very quick, especially if you turn off screen updating. I left out the part about making a copy of the worksheet first, to preserve the original data, but you already know that! HTH, Eric Sub Macro3() ' Unfilter the data * * ActiveSheet.ShowAllData ' Select all the relavent data, not including the header * * Range("A2:B9").Select ' your range will vary! ' Autofilter the data by EXCLUDING the desired manager's name * * Selection.AutoFilter Field:=1, Criteria1:="<John Doe", Operator:=xlAnd ' Select only the visible cells... * * Selection.SpecialCells(xlCellTypeVisible).Select ' Delete the selected rows * * Selection.EntireRow.Delete ' Select the header cell * * Range("A1").Select ' Unfilter * * ActiveSheet.ShowAllData End Sub Thanks! That worked great. Particularly after turning off the screen updating. Chris M. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Making a spreadsheet calculate faster | Excel Discussion (Misc queries) | |||
Making Windows XP Start 60% Faster | Excel Discussion (Misc queries) | |||
Making the macro's execution faster | Excel Programming | |||
Macro to call database table needs to process faster | Excel Programming | |||
making macro run faster? | Excel Programming |