Macro Help

0

The workbook has 3 sheets: Summary, Filtered and Data.  A CSV file is imported into the Data Sheet.  A macro is used to import certain columns from the Data Sheet into the Filtered Sheet.   The Summary Sheet is utilized to summarize the information that is filtered in the Filtered Sheet.   Sample sheet is attached.  Issues.

1.      Currently, before running the Get Column Macro I must delete all data in the Filtered Sheet.  This is no biggie except deleting the data also deletes my filters.    How can I preserve my filters?  Can I add a filter selection mode on the Summary Sheet for the Filtered Sheet?  Or is there a better way to preserve the filters?

2.     The Get Column Macro works but I prefer the columns in a different order but the macro places the columns in alphabetical    order not the order I prefer or how the code is listed.    How can the code be changed to place the columns in a desired order?

3.     Can I add code to the macro to auto delete the data in my Filtered Sheet so I don't have to manually delete the information prior to running the Get Column macro?

Thank you

Answer
Discuss

Discussion

Hi
1. I have had a look at your workbook and I do not see any filters. Filters are usually applied using the Data > Filter menu items. Your Filtered sheet appears to be a selection from your data sheet rather a filter.

2. The columns do not appear to be in alphabetical order so I guess you have already rearranged them to the order you like. Would easier to directly reference the cells rather than run the macro. for example, in the filtered sheet in cell A1 you could enter =Data!U1 you could set this up in any order you desire. Alos when you refresh your data your filtered(selected) sheet would be done with no further actions required. 

3 - my suggestion for 2 will do this  
k1w1sm (rep: 7) Jun 12, '18 at 6:48 pm
Add to Discussion

Answers

0

 Hi 

I have attached your spreadsheet with a new macro to selectively select the data from the datasheet and copy it to the filtered sheet.

I did not have time to finish it but you should get the idea.

Also a macro to flick the column letters to numbers and back again which I fin very useful 

Discuss

Discussion

Just wanted to let you know that your file wasn't attached.
don (rep: 1322) Jun 12, '18 at 11:44 pm
I left the filters turned off on purpose.  I wanted to make sure the sample data was visible on the filtered sheet.  On the summary Sheet I listed the columns as an example of how I wanted them listed in the filtered sheet with their column letters as shown on the data sheet and the filter criteria.  Since the data sheet import file is constantly changing I needed a solution that will automatically expand the column cell reference from the data sheet to the filtered sheet.  Thus, the Get Column macro filled that need and didnt require the constant adding more formulas to rows.  Or the chance of additional zeroes as was the issue in my original post Help, Too many Zeroes which caused a huge file size. But, THE Get Column macro required deleting the data before running the macro and my filters were deleted as well.  This is the reason for this post. Your file was not uploaded and I would be very interested in learning more about the macros you included into the file.  Will you please upload the file? Thank you  
Why Me (rep: 2) Jun 14, '18 at 1:14 pm
That is the second time I have had the files, not added to the original response, and I have had to add them as an edit. I will try harder.
There is something very wrong with Filtered sheet as I am unable to delete the empty rows in the used range.  It looks like Variatus' answer is a better option  
k1w1sm (rep: 7) Jun 14, '18 at 7:11 pm
Add to Discussion
0

I don't think you need any filters or VBA. In fact, you don't need the Filtered tab. You can have a better result, however, with better preparation. The concept I suggest is that you enter your filter criteria on the Summary sheet and use SUMIFS() functions to extract the totals from the Data sheet. When the data in the Data sheet change so does the summary. It's that easy - but not very easy to implement. That's why the first step should be to make it easier. The tool to use is the "Named range". Please read up about named ranges if you aren't familiar with them.

In the attached workbook I added a sheet New Summary which is a copy of your Summary sheet but with changes in it. First I added the rows 3:5 where cells C3:C5 contain three named ranges Year, State and Status. The purpose of naming these cells is simply becase "Year" is a better description than "$C$3". Your formulas will be easier to read.

In F8:F9 I explain how to create a dynamic named range. You need this because your data vary in size. The advantage in naming it is that you can refer to the range by the same name whatever its size might be. The range changes, the name stays the same, the formulas referring to it remain the same.

On the Ribbon's Formulas tab, click Define Name. In the dialog box that opens, enter the name at the top, a description in the middle and the definition of the range in the Refers To field at the bottom. The current selection is prefilled there but that is no use to you for dynamic ranges (good for the Year, State and Status, though). In essence you need a formula like this one:-

[F8] =OFFSET(Data!$V$2,0,0,8,1)

In essence, this formula tells Excel to select a range starting from V2 (on the Data tab), expand it by 0 rows and 0 columns, and then give it a height of 8 rows and a width of 1 column. That would end up being Data!$V$2:$V:9 - but not dynamic. The formula below finds the last used row in Data!A:A. This is important because I presume that column A will always have the maximum number of used rows. There is a -1 at the end because we start in row 2. The number of rows on the Data tab is the same for all columns. Therefore we can use the reading from column A for all column ranges.

[F9] =SUMPRODUCT(MAX(ROW(Data!$A:$A)*(Data!$A:$A<>"")))-1[CODE]
In the formula below the 8 was replaced with the formula in F9. Go to the Names Manager. You will find the formula from F10 in the Refers To field of the named range Dates. This range will now expand and shrink with the amount of data on the tab.
[CODE][F10] =OFFSET(Dates!$V$2,0,0,SUMPRODUCT(MAX(ROW(Data!$A:$A)*(Data!$A:$A<>"")))-1,1)

I set up named ranges States, Statuses and Prices as well. The formulas are in F11:F13 and all follow the same pattern. You will need more ranges but those I created are sufficient to try the SUMIFS formula. Here it is.

[C8] =SUMIFS(Prices,Dates,">=" & DATE(Year,1,1),Dates,"<=" & DATE(Year,12,31),States,State,Statuses,Status)

The syntax is SUMIFS(Sum_column, Criteria1_column, Criteria2, Criteria2_column, Criteria2, Criteria3_column, Criteria3, etc.etc.)
Applied: Sum the Prices column IF([Dates >= Jan_1_SelectedYear] AND [Dates <= Dec_31_SelectedYear] AND [States = SelectedState] AND [Statuses = SelectedStatus])

For your other fields the formula remains essentially the same for all your totals except for the sum_column for which you would have to set up the ranges first.

Discuss

Discussion

Hi Variatus
Sort of off topic. In the workbook I was not able to delete the empty rows in the used range on the Filtered sheet. Possibly goes back to the original question around lots of rows of zeros. Can you delete these empty rows? 
k1w1sm (rep: 7) Jun 13, '18 at 11:21 pm
Yes, this probably goes back to the way the sheet was created. I presume that OP cleared out the formulas from the area below row 9. Therefore the rows are blank but still counted within the UsedRange. I did a little research and found that the UsedRange can't, reasonably, be reset. One would have to delete all the contents and then save the workbook (perhaps close and re-open, too, but Save seems to be the important part after UsedRange.Clear).
My code might also have produced that sheet because it lifts the UsedRange from the Data tab. If the program that writes to the Data tab uses all rows then my macro would copy them to the Filtered tab.
Variatus (rep: 1318) Jun 14, '18 at 5:23 am
The original message I posted Help, Too many Zeros was due to the large file size the additional zeros created.    The file size was over 115MB but after starting over with the use of the Get Column Macro on the Filtered Sheet the file size was reduced to 2.7MB which is reasonable in size.  The file created by Variatus looks very interesting.   Ive never used ranges so a good thing to learn.  But, it appears the zeros are still in the cells even though I dont see them.  What am I missing? If there is a chance my CSV Import file is causing the zero issue then I need to get Step One right before I go to Step 2 and Step 3. Heres how I am importing the CSV file into the Data Sheet.   First, I imported the CSV file using the Import Text function under Data Tab.  This created a connection and I use the Refresh Button to gather a new CSV file when needed.  Ive checked the CSV file and it does not have additional zeros at the end of the last used cell. Maybe this is a new question how to import a CSV file into Excel.  This file is constantly updated so must be able to import into the Data Sheet so it can be filtered in the Filtered Sheet so it can be Summarize into the Summary Sheet.  Of course, the use of ranges may eliminate the filtered sheet.   Thank you
Why Me (rep: 2) Jun 14, '18 at 12:54 pm
My response, to your second post, demonstrating the use of reading the file row by row may be an option. You can then selectively choose which columns to download.  
k1w1sm (rep: 7) Jun 14, '18 at 7:16 pm
The Import Text function shouldn't append empty rows at the bottom of a sheet. The use of column addresses like A:A to paste formulas does. Therefore the latter is the reason for the zeroes in the Filtered sheet. You can check the size of the used range by selecting a sheet and typing ? ActiveSheet.UsedRange.Address in the VBE's Immediate Window. In the posted workbook the return for the Data sheet is $A$1:$IG$100 (indicating that 91 extra rows existed on that sheet at one time). For the Filtered tab the return is $A:$P.
BTW, you can use the same method to check if you have set up a new named range correctly ? ActiveSheet.Range("Dates").Address will display the effective range created by the dynamic formula if the sheet on which it is has been selected.
Variatus (rep: 1318) Jun 14, '18 at 8:05 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login