Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 24
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 10
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 24
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,934
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 24
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 24
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 30
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 24
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Making a spreadsheet calculate faster EricK Excel Discussion (Misc queries) 4 August 15th 07 10:08 PM
Making Windows XP Start 60% Faster Stranger Excel Discussion (Misc queries) 1 May 16th 07 10:06 PM
Making the macro's execution faster dspilberg Excel Programming 2 April 13th 07 02:50 PM
Macro to call database table needs to process faster Dan Excel Programming 2 March 1st 07 04:11 AM
making macro run faster? goorooj Excel Programming 5 February 19th 04 05:16 PM


All times are GMT +1. The time now is 04:01 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"