Excel/Follow up excel

Advertisement


Question
Andrea,
I'm afraid you assume I am more handy with Excel than I am.  If you want to still give this another try...

Your point about A:F doesn't help.  I already highlighted all the spreadsheet with my mouse and tried to copy it, but got the message that it was too large for the clipboard.  I don't know any other way to copy stuff.  You said .Range("A1 etc.").Copy.
I don't know what ".Range" means.  Is that some kind of copy tool?  As I said, the only way I know to copy is to highlight with mouse and then right click Copy, and the range would be A1:F6516, but again, that is too large for the clipboard.

Now this is just for your initial advice.  I don't know anything about macros.

Answer
Art - I'm sorry! I didn't mean to leave you out in the cold.

I created the macro for different reasons. Most importantly, I could not see a way to get the results you want manually. You can run AutoFilter by pressing the button, but without being able to specifically filter out the Grand Total and only after you'd performed the cumbersome copy & paste. And ultimately, the AutoFilter won't work unless the SubTotal is modified and then the SubTotal doesn't work right again. Which is why I wrote the macro to handle all of these problems.

If you are not comfortable with macros this might not be the right one to start with. I will have to refer you to another Expert, hopefully they will be able to see a solution I cannot. If you want to give a shot at using the macro, read on --

You do not have to copy or filter the data, the macro will do that. You do not need to understand the entire macro, just the two things you may in the future need to modify:

1. The size of your table changes. Anywhere you see "A:C", which just means columns A thru C, replace with the new size of your table: e.g. "A:F" or "A:B". Columns only, it doesn't need to know how many rows you have, it's just going to check them all. I tested this on a table with 6 columns and over 6,000 rows. A brief delay, but certainly better than doing it by hand.

2. You add or remove columns. Field:= is refering to a column (using a number instead of a letter, for some reason) but the number is the same as the column, so 1=A, 3=C, etc. In this macro Field:=3 is looking for any amount greater than 100 in column C. If for whatever reason the amounts are now in column E instead of C, just change Field:=3 to Field:=5 (5=E).

To run the macro, either go to your Developer tab and choose Macros, Run or right-click on the sheet tab, choose View Code, and hit the green triangle/arrow on the toolbar (then close the window with the red X and you'll be back in your worksheet).

Again, I did not mean to overwhelm you, and I hope you find the solution you need - Andrea
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Andrea Lynch

Expertise

I am very familiar with teaching excel concepts, and formulas. I am not as capable with VBA questions.

Experience

I currently tutor in this area. I worked for four years developing curriculum and teaching Microsoft applications to adult audiences. I also previously volunteered for allexperts.com.

Education/Credentials
BA, English, Western Washington University Certificates in C Programming, ASP.NET, VisualBasic.NET, University of Washington Extension

Awards and Honors
Rated in the top ten instructors (national), New Horizons Computer Learning Center.

©2016 About.com. All rights reserved.