Tidying up code

[trobb]

Hi,

I have copied together some code on the internet to practice some VBA. The code below gets the job done but it is very long and I am pretty sure it can be cut down. I have tried to shorten it but I have not a clue. I have this sum line of code that adds up fields that are returned when I select a certain sales rep.

to shorten the code I need to do something like this (doesn't work) so I can format the sheet in code rather than repeating it for J,K,L,N,M,O,P and Q.

[ThisWorkbook.Sheets("Sheet1").Range("J,K,L,N,M,O,P,Q" & lastrow + 1)]



If someone could help me be more succinct that would be great!!

spreadsheet attached if that helps.

Here is the code that I have. It returns the values from a dataset I have and returns a little report based on the rep name I select. I then try and add a total line totaling the numbers and putting a dash in where there aren't numbers.

Please Login or Register  to view this content.

Thanks

[jindon]

See if this is how you wanted.
Used AdvancedFilter instead of loop.

Please Login or Register  to view this content.

[MarvinP]

Hi trobb,

This problem can be done without any VBA or advanced filters. See the attached that gives a Pivot Table answer. You need to format the Pivot Table to not show subtotals and in Tabular format. You then simply filter by the Rep. See the attached. No VBA needed!! Note: I guess I'm just lazy and want to use the easy method for doing these things, and I do write VBA.

PT instead of VBA.xlsm

[jindon]

trobb

Thanks for the rep.