Search results

Ok Here is the coding to achieve what you require. Put all your countries in the Green sheet I have made for you. The procedure will handle all the countries you put in the Green sheet. The following is the VBA. Option Explicit Sub CountryMaker() Dim lr As Long Dim ar As Variant Dim i As...

Hi Mike So the only thing to identfy each of the cells as being UK for example can be 1 ,2, n... rows above the actual row the data is contained in. It is very safe to say this is a questionable spreadsheet design. I feel for you if you are getting it from elsewhere. The best thing to do...

Hi Farhana Welcome to the Chandoo Forum. I opend your file and saw no such names (Jack Russel, Jamie Banks?). You most likely mean column B but please make your file match the description. This avoids confusion. Take care Smalman

Hi This should also get you over the line. Sub CopyIt1() Sheets(Array("Sheet1", "Sheet3")).FillAcrossSheets Sheet1.[a1].CurrentRegion Sheet3.[a1].CurrentRegion.Value = Sheet3.[a1].CurrentRegion.Value End Sub Take care Smallman

Hi If you are using Excel 07 or later: =iferror(YourFormula,"") Excel 03 or earler =IF(ISERROR(YourFormula),"",YourFormula) Take care Smallman Edit - posted within a minute of the above :) No visibility.

Hi Lesley This is a non looping alternative. Sub CopyIt() Sheet1.[a3].Copy Sheet2.Range("C3").Resize(Sheet2.[c2], 1).PasteSpecial xlPasteValues End Sub Take care Smallman

Hi ED While this is a frequently asked question the code to achieve this task can be a long way from succinct. This should get you over the line. Option Explicit Sub SplitMe() Dim ar As Variant Dim i As Integer ar = Sheet1.Range("A4", Range("A" & Rows.Count).End(xlUp)) For i = 1...

Yeah - I changed the sheet names, your souce sheet became - start and the output sheet I originally titled end. Now you have a procedure to sum your data and delete your data, it looked to match your expected results. Take care Smallman

Loves it!!! I think the thing to note here is it is not only the people Narayan has assisted with his own time, it is also all the people who will find his sagely advice in years to come. That group will far exceed the one person he reached out to help with his vast knowledge. This goes for...

Hi When this occurs you need to create a trap for the last used row and execute the code only if the last row is greater than row 8, so: Sub Consolidate() Dim ws As Worksheet Dim lr As Long For Each ws In Sheets(Array("T-12", "T-9", "T-5", "T-4")) ws.[W8:W309].AutoFilter 1...

Hi Bit slow on this one but here is my take. Option Explicit Sub SplitMe() Dim ar() As String Dim var As Variant Dim str As String Dim i As Integer Dim j As Integer str = " 9 1.25 9.26e05 " ar = Split(str, " ") ReDim var(0, UBound(ar)) For i = 0 To UBound(ar)...

Hi Littleme I opened your file..... You have 37 named ranges. So you need two named ranges between the cells in Yellow. Is that up to and meeting the cells in yellow? It is probably possible but it is very difficult to make dynamic named ranges when you use the range below the range for...

OK Using your file as a baseline the following should cover off your requirement. Take note of the Yellow cells I put in your model. Option Explicit Sub SumandRemove() Dim ar As Variant Dim i As Long Dim j As Long Dim n As Long Dim str As String n = 2 ar =...

After further checking it works for all years. I got fooled by leap years but the correct results are spitting out. Take care Smallman

Hi I knocked this up quickly. Works for 2015. I need to work on it further to add flexibility for multiple years. Take care Smallman

In the mean time, here is something to go on with. Option Explicit Sub Consolidate() Dim ws As Worksheet For Each ws In Sheets(Array("T-12", "T-9", "T-5", "T-4")) ws.[W8:W309].AutoFilter 1, "New" ws.[A9:U309].Copy Sheet5.Range("A65536").End(xlUp)(2)...

Ah You have touched on an age old question. "There are lies, damn lies and statistics" goes the famous quote popularized by Mark Twain. If I have $10 and someone gives me $5, have I increased my stake by 50% or by 33%? Certainly I now have 33% more money than I did before but I have 50%...

Hi Happy to take up your problem provided you give a bit more clarity. What do you mean by spanning between empty rows? Can you provide an example to show a bit more detail. Take care Smallman

You are very welcome Mr Karr. All the very best Smallman

Hi Mark You have only provided part of your code. These: strPageName strRowName strColumnName strDataName Are only mentioned at the start and end with no variables assigned in the middle. This should format your cells Sub Format() Rows("1:5").Delete Shift:=xlUp...

Hi shibulal There is a link conservatively titled 'Dashboard' in post 3 above. At the bottom of that page is an Excel file. Can't miss it the link has the Excel symbol on it. I am sure you will find it. Take care Smallman

Hi Your solution is in your post. Create a new slicer and make damn sure that the new slicer does not have the same name as any other slicer. You need these two slicers to have unique names. Take care Smallman

Hi Don If your data is always the same in each group why do you need a loop? Why not just take what was suggested and do everything without a loop. I can't see why you would want to treat the cells independently. Take care Smallman

Hi Rubik See file attached. Take care Smallman

Hi This bit; Yes it is very possible. Here is a Dashboard I put together covering the topic. The key is to name your shapes exactly the same as the names in a drop down or whatever method you use to call each shape. Then use the Indirect formula to reference your drop down cell. Look at...