Search results

Update I've just created a spare column at the end and tried this code: MyObject.DataBodyRange.Columns(26).FormulaR1C1 = "=round([@Cost],2)" Application.Calculate MyObject.DataBodyRange.Columns(12) = MyObject.DataBodyRange.Columns(26).Value...

Hi Chandoo, I've got a massive table of 25 columns and 50,000 rows which I'm creating by copying and pasting selected columns from a larger raw data table. I'd like to round to 2 decimals the values that I'm copying from the raw data table either during the copy/paste process or afterwards...

All good Marc. English is not my first language either so I understand where you are coming from. Thanks for your help in resolving this.

That worked Marc L. Thanks Glad you removed your previous comment because it was unfair and insulting.

Vletm The file naming conversion is IndustryType-CityName-YYYYMM. This is used for both xlsx files and csv files. I want the user to pick the correct month and year from the CSV files not the XLSX files. The problem is that both file types show and I do not want the user to pick and XLSX file...

If you check my code, there are no wildcards on the path. The issue is filtering on the name and on the file type. Do you know what specific changes I should make to fix the code? Thanks for your suggestion but I can't follow it. If the user picks the wrong file, the information uploaded would...

Hi vletm, I reread the reference you posted but it's very basic and doesn't deal with my specific issue. Do you have any suggestions on the code?

Hi Chandoo, I'm trying to filter the files that show on the open dialog box to include certain strings but also to be csv type. Using the code below, the string filters work but not the file type. I've also tried to only filter on the file type but then I get all the csv files. MyPath...

Hi GraH - Guido, Thanks for your reply. I already have the Clear all button on my quick access toolbar but notice the difference between the two screenshots. The first one shows that my table is filtered and the Clear all button is enabled because my cursor is on the pivot table. The...

Hi Chandoo, I've created a pivot chart with many slicers around it. I've hidden the pivot table so users can focus on the chart and slicers. To remove filters, I either need to unhide, select the pivot table and click on clear all or remove individual filters from each slicer. If I don't click...

Prefect! Thanks Debaser!

Hi Chandoo, I've set up a large excel table (23 x 32000) and created many pivot tables and pivot charts on separate sheets. Each pivot table or chart has 5-6 slicers link to it. I'm trying to create a macro in Personal.xlsb to set the same properties in each slicer by loop through all of them...

I don't think that's relevant in this case but I got what I was looking from Peter. Thanks

Thanks Peter. Ctrl+; is simple enough for everyone to follow.

Hey Chandoo, I have a curly one for you. We've created a sales order form template that we sent to our suppliers to complete and send back. We have a today() formula to refresh the form while the customer fills it in together with the details of the order. The customer then emails the...

Thanks vlem. So I take it that the two versions cannot coexist in one query

Vletm, Do you have a specific code to give instead of a reference? I don't want to waste anyone's time here. Thanks Costas

Hi vlerm, I can't see how the previous post is relevant to what I'm trying to do. Maybe I wasn't clear enough so it's best if I give an example. If in a folder I have some files starting with NSW_* and some others starting with VIC_* and others with QLD_* and others starting with ALL_*. If...

Hi vletm, Thanks for your reply. I followed the link and tried it but couldn't get it to work. I understand the loop but don't know how to get it to see the the different cases (e.g. NSW or ALL). I'm not sure if the example is incomplete or if I'm not getting it. Can you please spell it out a...

Hi Chandoo, I'm using the .InitialFileName to filter on MyLocation which can be NSW, VIC or QLD. This works fine but I also need to filter on both one location and nationwide e.g. NSW or ALL. I tried typing MyLocation &";ALL" but no joy. Here is my code: Sub FileOpenDialogBox() MyPath =...

Hi Chihiro, That's exactly what I was looking for. Many thanks for your help!! Costas

Hi Chihiro, Many thanks for your reply. I'm using Office 365 but couldn't get the filter function to work but the second option works fine. My only problem now is that the drop down of Output E1 still shows all states instead of just the ones for the country selected in Output B1. I attach...

Hi Chandoo, I've got a tricky one to ask. I've got an excel table with a list of countries, their states and territories and major cities. I want to create a drop-down of the countries and another drop-down of states and territories that filters to the country selected in the first drop-down...

Hi guys, You are both right. The .filters.add only filters the file extension and it's in the .InitialFileName that you can filter on the file name. Thank you very much for your help!

Hi Chandoo, I'm trying to filter the file name to contain a specific string so it only shows files that have this string. When I try it I get a run-time error 5, invalid procedure call or argument Here is my code: Public MyFullPath as String Public Const MyLocation As String = "SYD" Sub...