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

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

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.

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.

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

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.

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

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.