Search results

Hi Turtel Notice how your ranges are varying lengths. That won't work. Square your ranges up and you should be good. =COUNTIFS(January!$B$7:$B$227,B5,January!$M$7:$M$227,"p") Take care Smallman

Hi Hamish Thanks for posting back with your feedback. Now anyone following the thread with the same issue will have to tools to solve the problem. Take care Smallman

Hamish Do you have hidden worksheets? Do you have merged cells? I am just guessing here. How about you open a fresh workbook and run my coding. It runs well on a test workbook. Now all you have to decide is what is different from your workbook to the test one. If you can not figure out the...

Hi Throttlewoks Thanks for your kind words. I am holidaying on a boat on the Great Barrier Reef right now. Tennis I still play two competitions a week. Leading one and mid table in the other one. It is good fun away from everything else in life, thanks for asking. Smallman

Hi CorrieAnn Sorry for the delay. I am on a boat right now. Have a look at the file attached. It will hide blank rows. I used exactly the same coding as you have above and it seems to work fine. If I misunderstood and you need blank and 0 then this code is slightly different again. I have...

Hi Asalamk You will notice every time you press the button on the Master sheet only one consolidation happens. The problem you were seeing was your master Sheet is Sheet6. This should help. Take care Smallman

I Corrieann I will post you am example file later tonight australian time showing this technique working on your range. If that does not work, change your range. Take care Smallman

Hi @asalamk The line where I clear the master sheet before running the code ensures that what you Describe can't happen. I ran it. Only produced one result no matter how many times you run it. Can you please elaborate a bit more by posting your file. Take care Smallman

Thanks very much all. It is very nice to be invited into such a strong community of learning. The site has really taken off since moving to this new platform and is a wonderful place to visit on a daily basis. I look forward to contributing whenever I can. Shrivallabha put it so...

Hi Guys When referring to anything in VBA the need to select or activate anything is almost never necessary. Dispensing with this saves time as items don't get selected prior to consolidation which means code runs more quickly. There are other ways to achive this task but this is the way I...

Hi Hamish For a Vb solution something like this should see you over the line. Sub ColourMeBad() Dim ws As Worksheet For Each ws In Sheets ws.Cells.Font.ColorIndex = xlAutomatic Next ws End Sub Take care Smallman

Hi Hamish To do this quickly. Click on the left most sheet. Now hold shift and click on the right most sheet. All sheets should appear White. Now Press Ctrl A. Choose the font colour you want. All sheets will show the font colour you just chose. Take care Smallman

Hi CorrieAnn For the autofilter procedure null change the'"<>0" to "<>" for the other procedure just change this line If Len(rng.Text) = 0 Then Should help anyways. Take care Smallman

Hi All Thank you for your kind words and for making me feel so welcome this past 14 months. It has been a pleasure to be here. This forum has some exceptional talent and it is a wonderful learning resource. I am proud to be a part of the chandoo forum. Thanks once more. Marcus (Smallman)

Hi CorrieAnn I did not provide a file to show how the above worked so my bad. You said in post three you want to delete the rows. I thought you just wanted them hidden. I will provide a file but a file from you would be more valuable as I will make certain assumptions. The file uses...

Sorry for the delay - dinner. Misses cooked Lamb. Here is what I spoke of: Sub MoveCol2() Dim ar As Variant Dim i As Integer Dim j As Long Dim n As Integer Dim ws As Worksheet Dim r As Range Dim lr As Long [M2:N500].ClearContents 'Set the Array Values For n = 1 To Worksheets.Count - 1...

Hi Coolkiran In answer to your post via PM - In order for you to generate a list at run time you will need to create a list with unique values from all of the header rows in a spreadsheet. As you are doing this procedure with VBA the fastest way to generate a unique list would be to Copy the...

@coolkiran When I said this in Post 2 I was saying what you said in post 5. I was already thinking about it from a broader perspective. Yes you should create a list in Excel. Lets say your list was in Column M of Sheet4 (your consolidation sheet). CHand the code from your post 5 to ar =...

@coolkiran I realise your column Name is not fixed. If you think about what I have done - really think about it - you will see the logic and its simplicity. It is scalable for your problem. Can you upload a situation where it might fail as it should work on many levels. I can create the...

@coolkiran Your problem is an odd one as all of the columns are mixed which you don't see that very often. In order to solve the puzzle you have to trap the columns. I may have oversimplified the problem as there may be a whole heap more column headings to search for. In which case I would...

Hi Joe The numbers look like they are feeding through to the chart OK. This is not a big thing but I would change the look of the output page. Pick 3 colours (4 tops) and go with these. Just because we are moving into the Christmas quarter doesn't mean our spreadsheets need to look...

Hi CorrieAnn I would do this slightly differently. I am not sure what your data looks like above line 36 but this might be an option Sub Hide() [A35:A125].AutoFilter 1, "<>0", , , 0 End Sub Sub Unhide() [A35:A125].AutoFilter End Sub If you want to treat one line at a time (the...

Oh - are you trying to find the PMW file location? That is a different question to can you look at this bit of code and tell me what it is doing (see post 1). Now I am very confused. What are you trying to achieve from this thread Clarence?

Clarence You made me laugh. Thanks. You don't understand. It is just saying don't close the personal macro workbook. It doesn't matter if you have one or not. That file does not come with a PMW. It is unique to each user. You can't send me your PMB or anyone else for that matter (not...

If you don't have a PMW hit record new macro - choose the personal macro wb as the source from the drop down and now perform an action, select a cell etc. Now press alt f11. Your PMB will now be on the vbe explorer window to the left. It will have the macro you just recorded. I don't use a PMB...